Passing Empty Parameters To Stored Procedures
Aug 9, 2000
Hi
I have a stored procedure some thing like this..
When I pass empty strings to both the parameters ..it is
returning all the rows from the table.
IF I pass both empty strings to @LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7),it should not select any rows from the table.. can any one suggest me as to how to accomplish this..
CREATE procedure Ceb_Phone_Book
@LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7)
AS
Select
EM010132.DEPARTMENTCODE_I,
DEPARTMENTNAME_I,
LLASTNAME_I,
FFIRSTNAME_I,
EM010132.WORKPHONE_I,
EM010132.MSTRING_I_5
FROM
EM010132 INNER JOIN HR2DEP01
ON HR2DEP01.DEPARTMENTCODE_I = EM010132.DEPARTMENTCODE_I
WHERE
INACTIVE = 0 AND
LLASTNAME_I LIKE LTRIM(RTRIM(@LLASTNAME_I)) + '%' AND
EM010132.DEPARTMENTCODE_I LIKE LTRIM(RTRIM(@DEPARTMENTCODE_I)) + '%'
ORDER BY
LLASTNAME_I,FFIRSTNAME_I
Thanks
VENU
View 3 Replies
ADVERTISEMENT
Feb 17, 2005
Hello,
I seached around for an answer to this question but didn't have much luck. Hopefully someone can help.
I am passing two parameters from a web page to a stored procedure. The first paramater @Field is the name of the field in the database I want to search, the second @Value is the value to seach for. The @Value works fine but the SP does not seem to recongnize the field parameter. I'm not sure if what I am attemping is not supported or wheather I just need to format the @Field in a different manner. The code and stored procedure is below.
Thanks for your help, Gary
Here is the web code:
Dim conMSS As New SqlConnection(ConfigurationSettings.AppSettings("dsnMSS"))
Dim cmdItems As New SqlCommand("DS-SPRS.dbo.s_ItemLookUp", conMSS)
cmdItems.CommandType = CommandType.StoredProcedure
cmdItems.Parameters.Add(New SqlParameter("@Field", SqlDbType.VarChar, 50))
cmdItems.Parameters.Add(New SqlParameter("@Value", SqlDbType.VarChar, 50))
cmdItems.Parameters("@Value").Value = txtValue.Text & "%"
cmdItems.Parameters("@Field").Value = lstField.SelectedValue
conMSS.Open()
dgdItems.DataSource = cmdItems.ExecuteReader
dgdItems.DataBind()
conMSS.Close()
Here is the stored procedure:
CREATE PROCEDURE s_ItemLookUp
@Field AS VARCHAR(50),
@Value AS VARCHAR(50)
AS
SELECT DIV_NO, DIV_NM, LN_NO, LN_DS, ITM_NO, PRD_DS, ITM_MFG_NO, VND_HFC_NM
FROM PRODUCT
WHERE @Field LIKE @Value
ORDER BY DIV_NO, LN_NO, ITM_NO
GO
View 4 Replies
View Related
Jul 20, 2005
I'm trying to pass parameters to an extended stored procedure, to noavail. I would like to pass two integers to the dll and I have thefollowing three snippets:1. The C++ portion of the dll:....declspec(dllexport) int myAddNumbers(int m, int n)....2. The creation of the extended stored procedure:EXEC sp_addextendedproc myAddNumbers , 'foodll.dll';3. The usage:create function TestFunction()returns integerasbegindeclare @rc integerexec @rc = myAddNumbersreturn (@rc)endHow do any of the above three things need to be modified in order tomake this work?Thanks!!!
View 1 Replies
View Related
Jul 20, 2005
There is a form in an Access Project (.adp, Access front end with SQLServer) for entering data into a table for temporary storing. Then, byclicking a botton, several action stored procedures (update, append) shouldbe activated in order to transfer data to other tables.I tried to avoid any coding in VB, as I am not a professional, but I havefound a statement in an article, that, unlike select queries, form's InputProperty can't be used for action queries. Therefore, parameters can bepassed to action stored procedure only by using ADO through VB.As I'm not very familiar with VB, I had to search in literature.So, this is a solution based on creating Parameter object in ADO and thenappending values to Parameter collection.Please, consider the following procedure I created for passing parametersfrom form's control objects (Text boxes) to a stored procedureDTKB_MB_UPDATE:Private Sub Command73_Click()Dim cmd As ADODB.CommandSet cmd = New ADODB.Commandcmd.ActiveConnection = CurrentProject.Connectioncmd.CommandText = "DTKB_MB_UPDATE"cmd.CommandType = adCmdStoredProcDim par As ADODB.ParameterSet par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput)cmd.Parameters.Append parSet par = cmd.CreateParameter("@BATCH_NUMBER", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@STATUS", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@DEPARTMENT", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@PRODUCTION", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@SAMPLING_TYPE", adVarWChar, adParamInput,50)cmd.Parameters.Append parcmd.Parameters("@DATE") = Me.DATEcmd.Parameters("@BATCH_NUMBER") = Me.BATCH_NUMBERcmd.Parameters("@STATUS") = Me.STATUScmd.Parameters("@DEPARTMENT") = Me.DEPARTMENTcmd.Parameters("@PRODUCTION") = Me.PRODUCTIONcmd.Parameters("@SAMPLING_TYPE") = Me.SAMPLING_TYPEcmd.ExecuteSet cmd = NothingEnd SubUnfortunately, when clicking on the botton, the following error apears:"Run-time error'-2147217913 (80040e07)':Syntax error converting datetimefrom character string."Obviously, there is some problem regarding parameter @DATE. In SQL Server itis datetime, on the form's onbound text box it is short date (dd.mm.yyyy)data type. I have found in literature that in ADO it should beadDBTimeStamp.So, what is the problem ?Greetings,Zlatko
View 2 Replies
View Related
Sep 13, 2006
Greetings! This is my first ever post here, so please be gentle.
I have a stored procedure that will be accepting many parameters (around 10). Any number of them can be empty (empty with a length of zero, but probably not Null per se).
How do I do a Select... Where... where if the parameter is empty it ignores it in the 'Where' but if the parameter had anything in it, it becomes part of the filter?
Thanks!
View 5 Replies
View Related
Feb 23, 2004
Dear all,
i want to know how i can pass multiple values in the form of arrays to a stored procedures.
the technique by which i pass multiple values to a stored procedure beginning along with declarations are as follows:
Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader()
Me.cmdInsSlabHmst = New System.Data.OleDb.OleDbCommand()
Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection()
Me.cmdInsSlabDMst = New System.Data.OleDb.OleDbCommand()
'
'cmdInsSlabHmst
'
Me.cmdInsSlabHmst.CommandText = "PKGSLABHMST.INSSLABHMST"
Me.cmdInsSlabHmst.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdInsSlabHmst.Connection = Me.OleDbConnection1
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABDESC", System.Data.OleDb.OleDbType.VarChar, 50))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABUNIT", System.Data.OleDb.OleDbType.VarChar, 1))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iREMARKS", System.Data.OleDb.OleDbType.VarChar))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABFROM", System.Data.OleDb.OleDbType.VarChar))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABRATE", System.Data.OleDb.OleDbType.VarChar))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iNOOFRECORDS", System.Data.OleDb.OleDbType.Integer))
'
'OleDbConnection1
'
Me.OleDbConnection1.ConnectionString = CType(configurationAppSettings.GetValue("ConnectionString", GetType(System.String)), String)
'Passing multiple values to the procedure with the help of ~ sign
Dim strCode As String
Dim i As Integer
'Dim dblSlabRate As Decimal
'Dim dblSlabFrom As Decimal
Dim strSlabRate As String
Dim strSlabFrom As String
Dim strSlabRateP As String
Dim strSlabFromP As String
Dim intCntr As Integer
'Me.cmdInsSlabHmst.Parameters("iSLABDESC").Value = txtSlabDesc.Text
'Me.cmdInsSlabHmst.Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
'Me.cmdInsSlabHmst.Parameters("iREMARKS").Value = txtRemarks.Text
'OleDbConnection1.Open()
'strCode = cmdInsSlabHmst.ExecuteScalar
'OleDbConnection1.Close()
For i = 0 To dgSlabDtl.Items.Count - 1
If i = dgSlabDtl.Items.Count - 1 Then
'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
strSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
Else
'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
strSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
End If
strSlabRateP += strSlabRate & "~"
strSlabFromP += strSlabFrom & "~"
intCntr += 1
'If dblSlabRate <> "" And dblSlabFrom <> "" Then
'InsDtl(strCode, dblSlabFrom, dblSlabRate)
'End If
Next
If strSlabRateP <> "" And strSlabFrom <> "" Then
With cmdInsSlabHmst
.Parameters("iSLABDESC").Value = UCase(txtSlabDesc.Text)
.Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
.Parameters("iREMARKS").Value = txtRemarks.Text
.Parameters("iSLABFROM").Value = strSlabFromP
.Parameters("iSLABRATE").Value = strSlabRateP
.Parameters("iNOOFRECORDS").Value = intCntr
End With
OleDbConnection1.Open()
cmdInsSlabHmst.ExecuteNonQuery()
OleDbConnection1.Close()
End If
to the insert procedure i am passing multiple values with the help of ~ sign and in the procedure the individual values are separated by identifying the position of ~ sign and the no. of records which have been passed. For which a complicated stored procedure has been written.
i want to pass multiple values in an array, so that my stored procedure becomes simple and runs faster. So, if someone tells me how to pass arrays to a stored procedure (with code example), it will be of real help.
regards
subhajit
View 1 Replies
View Related
Feb 17, 2005
How to do this ?
==============================
CREATE procedure dbo.AddTb2FromTb1
@Tb1No nvarchar(1000)
as
insert into Tb2 (*)
select * from Tb1
where Tb1 IN (@Tb1No) /* How to Passing an Array to a Stored Procedures ??? */
==============================
dbo.AddTb2FromTb1 'No001' is Work !
dbo.AddTb2FromTb1 'No001,No002,Bo003' is not Work !
View 3 Replies
View Related
Feb 20, 2003
Someone recently tried to tell me that it is possible to pass an array to a stored procedure.
I have tried creating procedures with the 'table' datatype for the parameters but the attempts fail with a syntax error.
We currently workaround this limitation by passing the equivalent of an array to temporary tables and selecting from those tables as needed within our stored procedure but if we can circumvent this by passing an array, we'd definetly like to try that instead.
Thanks in advance for any advice you may share.
View 4 Replies
View Related
Jun 7, 2007
Hi guys,
I'm new in SSRS, Im having problems passing multivalue in stored procedures. Can someone help. please.
Thanks in advance.
View 10 Replies
View Related
Apr 11, 2007
I have two variables in my code behind .cs,
string Zip;int MenuCode;
I am trying to pass them to the stored procedure below and am getting compile errors. How do I properly construct the parameter object?
error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string"
<asp:SqlDataSource ID="LocalPremiumLinks" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="Zip" Type="string" /> <asp:Parameter Name="MenuCode" Type="double" /> </SelectParameters>
View 1 Replies
View Related
Mar 8, 2004
Hi, I'm fairly new to TSQL so I'll try to explain as best I can.
I'm using a stored procedure to return a recordset and I'm passing a variable/parameter into it from MS Access XP, the syntax for the SQL statement is something like the following (Extremely trimmed down) :
CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(8)
AS
declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode = @MyLsn)
The value I'm passing (@MyLSN) is 24/12359 (one LS Number)
The above statement works fine (ie returns a recordset). But what I want to be able to do is pass a series of LS numbers into the Stored Procedure. So I've opted for using the SQL 'IN' function. So the SQL statement now looks like:
CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(50)
AS
declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode IN (@MyLsn))
The value I'm passing (@MyLSN) is '24/12359','24/12360'
This is where the problem begins because the stored procedure now doesn't return a recordset.
If I run this SQL statement thru a VIEW (manually entering the LS numbers) then the correct recordset is returned.
Also if I run the Stored Procedure using the Query Analyzer (where it will prompt me for the value of @MyLsn) and enter just one LS Number then it works fine but if I try to supply 2 LS Numbers then nothing is returned. I've tried all manner of syntax when supplying the LS numbers but alas to no avail.
So if someone could point me in the right direction then that would be much appreiciated.
Thanks in advance.
View 9 Replies
View Related
Sep 19, 2013
I've created a stored procedure and I need to pass it a list of order IDs.The OrderSourceOrderID field is NVARCHAR(100) in the DB. <-Not sure if this piece of info makes a difference.
It all works, except for the @orderList variable. I'm listing OrderSourceOrderIDs that I do not want to appear, but they appear in the final list anyway. How would I pass this information the right way?
EXEC uspGetBuyCancelledOrders @orderList ='''12343'',''1122'',''123123132''', @companyID='123'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
View 11 Replies
View Related
Oct 23, 2007
I have created a stored proc for a report, which works fine. I want to have the user enter a project ID to filter the report, so set the stored proc accordingly. However, I want the user to enter a 10 digit ID, which is the equivilent of two fields in the stored proc. My where statement is :
Where actv.ProjID + '-' + actv.Activity = @project
This works fine under the data tab, I can enter a full project and get the results I want. But I cannot preview the report without getting an error. I'm not sure how to add this to the report parameters, as it is two fields concatenated together. Any help would be appreciated!
View 3 Replies
View Related
Nov 22, 2006
Hey people,
I am trying to pass a parameter to a stored procedure based on a select statement. Basically it's something like this (in my head :P ):
foreach PersonID in (SELECT id FROM person)
{
MyStoredProcedure(PersonID);
}
Multiple rows will be returned from the select statement.
The code above will be in another stored procedure (not that code but an equivalent in SQL). Is there something like a PLSQL in SQL Server 2000? How can I translate that into a stored procedure for SQL?
Thanks to you all!
View 1 Replies
View Related
Aug 29, 2006
Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!
View 4 Replies
View Related
May 31, 2001
Can anyone tell me the correct syntax for passing a Table Name to a Stored Procedure that uses the Table Name to do an INSERT into that Table ?
Below is my poor effort at the SQL.
Thanks.
CREATE PROCEDURE InsertOrderLine
@OrderNo INT,
@ProductID VARCHAR(15),
@UnitCost MONEY = 0,
@Quantity FLOAT = 0,
@Discount MONEY = 0,
@Tax MONEY = 0,
@TABLENAME VARCHAR(200)
AS
DECLARE @SQL VARCHAR(1000)
SELECT @SQL='INSERT ' + @TABLENAME + ' (OrderNo,
ProductID, UnitCost, Quantity, Discount, Tax) VALUES
(@OrderNo + ',' + @ProductID + ',' + @UnitCost + ',' + @Quantity +
',' + @Discount + ',' + @Tax)'
GO
View 1 Replies
View Related
Aug 22, 2006
Hi,
I'm developing a website using vwd express and I have created a GridView that bounds data from a stored procedure. The stored procedure takes one parameter. I tested it by using a default value and it works fine.
Now, instead of the default value i want to pass the current logged in user name as a parameter.
How do i do this. All the info i found around are for passing parameters to the select command of sqldatasource but i cant get it to work when i use a stored procedure.
Thanks, M.
View 4 Replies
View Related
Feb 14, 2007
HI all, I'd like to run a simple stored procedure on the Event of a button click, for which I don't need to pass any parameters, I am aware how to run a Stored Procedure with parameters, but I don't know how without, any help would be appreciated please.thanks.
View 6 Replies
View Related
May 26, 1999
I am trying to pass two parameters to a stored procedure using the code below:
Dim cmd As New Command
Dim rs As New Recordset
Dim prm As New Parameter
Dim ttt As New Parameter
cmd.ActiveConnection = cnn1
cmd.CommandText = "{call usp_PlcwithinUID_upt(?,?)}"
cmd.CommandType = adCmdStoredProc
Set prm = New Parameter
prm.Type = adInteger
prm.Value = gsPlcCode
cmd.Parameters.Append prm
Set ttt = New Parameter
ttt.Type = adVarChar
ttt.Value = "TEST"
cmd.Parameters.Append ttt
'cmd(1) = gsPlcCode
'cmd(2) = "Test"
Set rs = cmd.Execute
It works when passing one but NOT two ?? Can anyone shed any light ??
I've also tried using the Create Parameter method but again one is fine but passing two parameters doesn't work ?
The stored procedure just performs an update on a table.
Basically I want to be able to update any fields in a table (specific record) when they have been changed by the user. I have placed boolean variables in the change events to detect any user edits. If any field(s) have been edited I then want to send each field within the current record as parameters to the update stored procedure.
Any clues as to how to get an update stored procedure to except multiple variables.
I have trawled through loads of documentation to no avail.
Please help.
Paul
View 1 Replies
View Related
Feb 26, 2004
Hi,
I have a complex SQL query against multiple tables that ideally would be best used as a stored procedure.
I know of and have read about creating named parameters and setting the parameters' values in ASP just before issuing an execute command via the Command object. What I'd like to do is assign the text of the filter and sort clauses to the parameters. Not the values, but the entire string, such as
"WHERE lastname LIKE 'A%' "
"ORDER BY lastname "
I need to assign the entire clause because the query may or may not use a particular clause.
I have experimented. However SQL treats the parameter as a literal string rather than part of the SQL query itself. Is there another technique that I may use to accomplish my goal?
Thanks in advance for your opinion, suggestion, criticism, etc.
--Tom.
View 2 Replies
View Related
Jan 22, 2007
Hi to all,
How can I Pass different number of parameters to a Stored Procedure?
In my Requirement,
Some times i want to pass 2 parameters only,
In some cases i want to pass 6 parameters.
How can i do this?
Please give me a solution.
Thanx in advance...
View 1 Replies
View Related
Dec 22, 2007
I am trying to pass in parameters from MSSQL Store procedure to a MySql database but it doesn't take the parameters? Any idea how to achieve this? The query works if I pass in the actual dates.
Create procedure [dbo].[MY_STORED_PROC]
@STime datetime,
@ETime datetime
AS
select *
into #MyTempTable
from openQuery(MYSQL, '
SELECT a.* FROM mytable a
where a.createdate between @STime and @ETime
')
View 2 Replies
View Related
Mar 9, 2001
How can a Stored Procedure use a variable table name and column name ?
The statement :-
SELECT @columnname FROM @tablename
gives error "Line 5: Incorrect syntax near '@tablename'."
I am passing the parameters 'tablename' and 'columnname' into the stored procedure, (in order to select a variable column from a variable table).
If I hard-code the tablename, then I get a column of output with just the name of the column I was trying to list, e.g. surname,
i.e. SELECT @columnname FROM employeetable gives :-
surname
surname
surname
surname
surname
surname
How can I get the procedure to accept a variable table name and column name ?
I thought one way to do it would be to use the 'EXEC' statement :-
SELECT @sql = 'SELECT '+ @columnname +' FROM '+@tablename
EXEC(@sql)
but this gives error : "Incorrect syntax near the keyword 'FROM' "
although the following table works :-
SELECT @sql = 'SELECT surname FROM '+@tablename
EXEC(@sql)
The problem seems to be mainly with variable column names.
Also, we don't really want to use the EXEC statement because it will not be compiled and will be slower. (Does anyone know by how much slower ?)
Any advice would be appreciated.
Kind regards,
Ian.
(ian.mitchell@sds.no)
View 1 Replies
View Related
Mar 28, 2007
Hello,
I'm having trouble executing a Stored Procedure when I leave the input field empty on a 'search' criteria field. I presume the error is Null/Empty related.
The Stored Procedure works correctly when running in isolation. (with the parameter set to either empty or populated)
When the application is run and the input text field has one or more characters in it then the Stored Procedure works as expected as well.
Code:
.
.
<td style="width: 3px">
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
.
.
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="LogId" DataSourceID="SqlDataSource1"
Width="533px">
<Columns>
<asp:BoundField DataField="LogId" HeaderText="Log Id" InsertVisible="False" ReadOnly="True"
SortExpression="LogId" />
<asp:BoundField DataField="SubmittedBy" HeaderText="Submitted By" SortExpression="SubmittedBy" />
<asp:BoundField DataField="Subject" HeaderText="Subject" SortExpression="Subject" />
<asp:TemplateField>
<ItemTemplate>
<span>
<asp:HyperLink ID="HyperLink1" runat="server">HyperLink</asp:HyperLink></span>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<HeaderStyle BackColor="#608FC8" />
<AlternatingRowStyle BackColor="#FFFFC0" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SmallCompanyCS %>"
SelectCommand="spViewLog" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="txtName" ConvertEmptyStringToNull="true" Name="name" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Stored Procedure:
ALTER PROCEDURE dbo.spViewLog (@name varchar(50) )
AS
SELECT * FROM log_Hdr WHERE (log_hdr.submittedby LIKE '%' + @name + '%')
RETURN
I have tried the 'convertemptystringtonull' parameter but this didn't seem to work.
Any guidance would be much appreciated.
Thank you
Lee
View 2 Replies
View Related
Oct 9, 2007
Hi,I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:SqlDataSource1.SelectCommand = "_Search"SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureGridView1.DataSourceID = "SqlDataSource1"GridView1.DataBind()MsgBox(GridView1.Rows.Count) It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?Stan
View 2 Replies
View Related
Jul 9, 2006
I am fairly new to MSSQL. Looking for a answer to a simple question.
I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.
The structure of this table is something like
PKID
customerID
email address
.....
customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...
Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?
Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.
Any other ways to do this? Thanks
View 7 Replies
View Related
Aug 4, 2015
I am working on an Excel VBA report which is linked to an SQL Server database. The front end is Excel VBA routine, the backend is SQL Server stored procedure.
The VBA routine passes 2 dates to the stored procedure but it seems that it doesn't accept them.
The 2 date parameters in the stored procedure are @OrderDateRangeStart and @OrderDateRangeEnd.
Here is a portion of the stored procedure:
alter proc uspSalesCommission
...
...
@IncludeOrderDateAsCriterion int,
@OrderDateRangeStart date,
@OrderDateRangeEnd date
as
BEGIN
...
...
WHERE (@IncludeSalesPersonsAsCriterion=0 or Staff.name in (@Salespersons)) and
(@IncludeOrderDateAsCriterion=0 or SALESORD_HDR.ORDERDATE between @OrderDateRangeStart and @OrderDateRangeEnd)
In Excel VBA, the code passing the parameters are:
cmd1.parameters("@OrderDateRangeStart").value = cdate(me.startDate)
cmd1.parameters("@OrderDateRangeEnd").value = cdate(me.EndDate)
where cmd1 is a command object, me.startdate is start date field in Excel me.enddate is the end date field in Excel.
The Excel VBA routine works only when the day and month are both 1,(e.g. 1/1/2015), when they are other values (e.g. 31/5/2014) , it failed.
View 5 Replies
View Related
Jan 5, 2007
Hi peeps,
I need some help with passing parameters to a stored procedure from my visual basic code.
Unfortunately im a bit of a novice with Visual basic and therefore have very little experience with it.
I have written a stored procedure in VS 2005 which when executed from the server explorer appears to retrieve the results that I require. However I am at a loss for how to actually call this procedure from my visual basic code.
The stored procedure is fairly simple requiring 5 colums from 2 tables. The procedure requires a single parameter to be passed to it.
The code for the procedure is listed below:
/*
Name: usp_display_all_users
Description: Displays activeuser, personid, comment from table: pswds
Userid and sort from table: people
Where the username is like the parameter supplied.
Both tables joined on personid
Author: Iain Blackwood
Modification log: Change
Description Date Changed by
Created proc 02/01/07 Iain Blackwood
*/
ALTER PROCEDURE usp_display_all_users
(
@searchStr nvarchar(128) =''
)
AS
SELECT dbo.pswds.activeuser, dbo.pswds.personid, dbo.people.userid, dbo.people.sort, dbo.pswds.comment
FROM dbo.pswds INNER JOIN
dbo.people ON dbo.pswds.personid = dbo.people.personid
WHERE (dbo.people.sort LIKE @searchStr + '%')
ORDER BY dbo.people.sort
The Visual Basic application I am working on firstly requires login details from the user to build a connection string for the SqlConnection. Once these vaules have been succesfully retrieved the application should display a view with the data returned by the stored procedure (in this case the stored procedure should use the default input parameter value of an empty string to return every row of data from the tables). However I also require that the stored procedure be called if the user enters a search string into the relevant textbox.
I have managed to reproduce the view I require with the following code however this is using SQL commands passed directly to the an SqlDataAdapter and not by calling the Stored procedure that i have written.
Private Sub fillDataGrid()
' I NEED TO:
' 1: Fill the data set with all Accounts
' 2: Diplay the Data to the data grid
' delcare a new SQL connection
sqlCon = New SqlConnection(conStr)
' Delcare and build the SQL Command String: WILL BE REPLACED BY STORED PROCEDURE
Dim comStrPeople As String = "SELECT pswds.activeuser, pswds.personid, userid, sort, pswds.comment"
comStrPeople += " FROM pswds INNER JOIN"
comStrPeople += " people ON pswds.personid = people.personid"
comStrPeople += " ORDER BY sort"
' Display the command string: TEMPOARY
testlbl2.Text = comStrPeople
' Declare a new SQL data adapter
sqlDataAdapter1 = New SqlDataAdapter(comStrPeople, sqlCon)
Try
' Declare a new dataset
sqlDataSet = New DataSet
' fill the sql data adapter with data from dataset: called PeoplePswds
sqlDataAdapter1.Fill(sqlDataSet, "PeoplePswds")
' Fill the forms datagrid view with data from the Dataset table PeoplePswds
DataGrid1.DataSource = sqlDataSet.Tables("PeoplePswds").DefaultView
Catch ex As Exception
' Display suitable error message
MessageBox.Show("Unable to retrieve Account Data at sub fillDataGrid" + ex.Message)
End Try
End Sub
I Guess what im asking for is someone to show / help with how the stored procedure is called from the visual basic code and passed the parameter/s required.
Thanx Flakkie
View 6 Replies
View Related
Sep 24, 2002
Hi
I was wondering how I could pass on the following parameters from an ini file to a stored procedure within a DTS package. The parameters in the ini file look like:
[DatabaseCleaner]
! -- TableToBeCleaned_N=<table name>,<months to hold on db>,<months to hold on hd>
! -- <N> must be a successive number starting from 1 ...
TableToBeCleaned_1=Transactions,24,24
TableToBeCleaned_2=Payments,24,24
TableToBeCleaned_3=PresenceTickets,24,24
As I do not know how many tables that will be declared in the ini file I have to loop through until the last parameters and pass it over to the SP.
How can I do that? Any idea?
Thanks
mipo
View 1 Replies
View Related
May 14, 2008
Hi,
I have 2 source tables emp_ass,aprvl_status these tables are not having common column to join. and 1 target table Time_Card, i have a stored procedure with 4 input parameters, emp_ass_id,status_id,start date,end date,i am inserting data into timecard based on emp_ass_id, my week start date is sunday and end date is saterday if emp start date is sunday i am just incremnting the start date by 7 days as end date is saterday and inserting that row, if employe statrt date is other than Sunday. i am just insering start date with to reach end date saterday, this work fine when i give the input parameters, now my reqirement is i need to automate this process as i need to get new emp_ass_id which is not in target table and insert his records based on his start date and end date,
ex:
if emp_ass_id is 1001, start date 1/1/2008 and end date is 2/1/2008 then i need to insert
Uniq_Id, emp_ass_id, start_date end_date status_id
1099
1001
1/1/2008 12:00:00 AM
1/5/2008 12:00:00 AM 1
1100
1001
1/6/2008 12:00:00 AM
1/12/2008 12:00:00 AM 1
1101
1001
1/13/2008 12:00:00 AM
1/19/2008 12:00:00 AM 1
1102
1001
1/20/2008 12:00:00 AM
1/26/2008 12:00:00 AM 1
1103
1001
1/27/2008 12:00:00 AM
2/2/2008 12:00:00 AM 1
the stored procedure will insert these records if i give the input parameters, now i need to automate this process by using SSIS. please help me,i need to get emp_ass_id,start_date,end_date dynamically from source table if emp_ass_id is not in target table.
Thanks in advance.
View 9 Replies
View Related
Jun 20, 2007
Hello all,
I have written an XP for SQL Server 2000 SP2. It performs as expected if I call the XP with literal values for the parameters, however when I wrap the XP call into a regular stored procedure, only the first character of each input string is seen by the XP! Here are the relevant code snippets:
C++ Extended Stored Procedure:
(Basically all this code is doing is retrieving the parameters and printing them back out)
srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, NULL, &bNull);
param1 = new BYTE[uLen + 1];
srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, param1, &bNull);
param1[uLen] = ' ';
srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, NULL, &bNull);
param2 = new BYTE[uLen + 1];
srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, param2, &bNull);
param2[uLen] = ' ';
srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, NULL, &bNull);
param3 = new BYTE[uLen + 1];
srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, param3, &bNull);
param3[uLen] = ' ';
sprintf(msgText, "Params received by xp: %s, %s, %s", param1, param2, param3);
srv_sendmsg( srvproc, SRV_MSG_ERROR, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msgText,SRV_NULLTERM);
srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);
Calling the XP with literal values:
EXEC xp_mytest 'one','two','three'
Output:
Params received by xp: one, two, three
Calling XP via a stored procedure:
create procedure sp_mytest
(
@myvar1 nvarchar(200),
@myvar2 nvarchar(50),
@myvar3 nvarchar(50)
)
as BEGIN
PRINT @myvar1
PRINT @myvar2
PRINT @myvar3
EXEC xp_mytest @myvar1, @myvar2, @myvar3
END
EXEC sp_mytest 'one','two','three'
Output:
one
two
three
Params received by xp: o,t,t
Any insight or assistance is greatly appreciated!!!
View 1 Replies
View Related
Jul 30, 2007
Hello,
I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.
I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.
But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?
Thanks.
View 3 Replies
View Related
Nov 22, 2006
How many parameters can I use for a Stored Procedure.
View 4 Replies
View Related
