PROBLEM REGARDING VARIABLE PASSING TO STORED PROCEDURE
Sep 24, 2007
Hi
I am WORKING IN AN APPLICATION USING SQL SERVER 2000 AND VB6
I'VE A PROBLEM REGARDING VARIABLE PASSING TO STORED PROCEDURE
I WILL EXPLAIN WITH AN EXAMPLE
TABLE STRUCTURE
AccAccounts
------------------
Accid(Numeric) AccName(Varchar)
-------------------------------------------------
1 Cash A/c
2 Students A/c
3 HDFC Bank A/c
my Application will pass the "Accid" as a string format to Stored Procedure
STORED PROCEDURE
-----------------------------------
CREATE PROCEDURE GetAccName
@Accid Varchar(100)
AS
Select * from Accaccount where accid in (@Accid)
when i run this SP
declare @Accid Varchar(100)
set @Accid ='1,2'
exec GetAccName @Accid
i get the following error
Server: Msg 8114, Level 16, State 5, Procedure GetAccName, Line 4
Error converting data type varchar to numeric.
please "ANY ONE" help me!!.
The above example is only an example
REGARDS
JAMES
View 8 Replies
ADVERTISEMENT
Dec 7, 2007
I've got stored procedure:
ALTER PROCEDURE [dbo].[dropmyValue](@dropVal Char OUTPUT)ASEXECUTE('ALTER TABLE [dbo].[tbl1] DROP CONSTRAINT ' + @dropVal) That gets it's value from a GridView.SelectedValue:
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) If GridView1.SelectedValue.ToString <> "" Then Dim cs As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString Using con As New SqlConnection(cs) con.Open() Dim cmd As New SqlCommand cmd.Connection = con cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "dropmyValue" cmd.Parameters.Add("dropVal", SqlDbType.Char) cmd.Parameters("dropVal").Direction = ParameterDirection.InputOutput cmd.Parameters("dropVal").Value = "DF_" + GridView1.SelectedValue Label2.Text = cmd.Parameters("dropVal").Value cmd.ExecuteNonQuery() End Using GridView1.DataBind() End If End Sub
Label2.text shows that @dropVal is "DF_xxxxxxxx" and is the name of the Constaint to be dropped (when I comment out "cmd.ExecuteNonQuery" and run it), but the error I get is that
" 'D' is not a Constraint ". I don't know if this is a sqldbtype problem, but I've tried different ones and evidently only the first character "D" is getting read, or passed to the stored procedure.
Any help would be appreciated.
Steve
View 3 Replies
View Related
Apr 29, 2005
I'm trying to do something like this in SQL Server:
<code>
CREATE PROCEDURE sp_insert_proc
(
@item1 as int,
@item2 as varchar(50),
)
DECLARE @LocalVariable AS varchar(50)
SET @LocalVariable = dbo.sp_storedprocedure
INSERT INTO Table
(
Column1,
Column2,
Column3,
)
VALUES
(
@Item1,
@Item2,
@LocalVariable
)
</code>
Is this possible?
How can I return the results of the stored procedure to a variable so I can use those results
View 1 Replies
View Related
May 5, 2000
I need to execute a stored procedure which selects all columns from the passed table. The table used is a variable.
Select * from @Passedtablename. This won't work. Any insights.
View 1 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
Feb 25, 2008
I've created a varible timeStamp that I want to feed into a stored procedure but I'm not having any luck. I'm sure its a simple SSIS 101 problem that I can't see or I may be using the wrong syntax
in Execute SQL Task Editor I have
conn type -- ole db
connection -- some server
sql source type -- direct input
sql statement -- exec testStoredProc @timeStamp = ?
if I put a value direclty into the statement it works just fine: exec testStoredProc '02-25-2008'
This is the syntax I found to execute the procedure, I don't udnerstand few things about it.
1. why when I try to run it it changes it to exec testStoredProc @timeStamp = ? with error: EXEC construct or statement is not supported , followed by erro: no value given for one or more requreid parameters.
2. I tired using SQL commands exec testStoredProc @timeStamp and exec testStoredProc timeStamp but nothing happens. Just an error saying unable to convert varchar to datetime
3. Also from SRS I usually have to point the timeStamp to @timeStamp and I dont know how to do that here I thought it was part of the parameter mapping but I can't figure out what the parameter name and parameter size should be; size defaults to -1.
Thank you, please help.
View 2 Replies
View Related
Nov 6, 2002
Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.
TIA,
View 3 Replies
View Related
Jun 12, 2006
Hello,
I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.
When I try to exectue a similar task passing those variables as parameters, I get an error:
Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1
The stored proc is expecting datetime parameters.
Thanks for the help.
Mike
View 3 Replies
View Related
Feb 27, 2008
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
View 5 Replies
View Related
Oct 11, 2006
I have successfully Create a Site, Inserting Updating and
Deleting information in my DB all with Stored Procedures, But I need the
ability to pass their username into my Stored Procedures. How and where do I
code this in my ASPX file?
My SP would be something like this
Create procedure test
@UserName
varchar(50)
As
Select *
From table
Where username
= @username
All of the
data is tied to the user in one way or another but I do not know what code to
put in my page?
View 2 Replies
View Related
Feb 16, 2007
Anyone got an example of passing xml to a stored procedure and within that procedure, grabbing values out of the xml to perform an insert ?
View 1 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
Dec 10, 2007
Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors.
Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors.
The error i get is: Procedure or function 'SPOne' expects parameter '@publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails.
SPOne is as follows: ALTER PROCEDURE dbo.StoredProcedureOne @typeID smallint=null, @title nvarchar(MAX)=null, @publicationID smallint OUTPUTASBEGIN SET NOCOUNT ON INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) SELECT @publicationID = scope_identity()
END
and SPtwo is as follows: ASDECLARE @NewpublicationID IntEXEC StoredProcedureOne @NewpublicationID = OUTPUTSET @publicationID = @NewpublicationIDINSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID)
SELECT @NewpublicationID
Thanks
Gemma
View 8 Replies
View Related
Jan 25, 2008
HII m trying to use the following Store Procedure to search Suppliers... user provides a Column Name (Search By) and an Expression... but i m not getting it work properly... plz review it and tell me wots wrong with this stored procedure is... CREATE PROCEDURE dbo.SearchSupplier ( @Column nvarchar(50), @Expression nvarchar(50) ) AS SELECT SupplierID,Name,Address,Country,City,Phone,Fax FROM Supplier WHERE @Column LIKE '%'+@Expression +'%' OR @Column = @Expression RETURN Thanks
View 2 Replies
View Related
Mar 31, 2008
Hi i have some radio buttons, what i want to achieve is that if one of the radio buttons is clicked then the value is sent to the stored procedure, as as now the radio buttonlist is like this; <asp:RadioButtonList ID="SortButtons" runat="Server" RepeatDirection="Horizontal" RepeatLayout="Flow" Width="69px" OnSelectedIndexChanged="SortButtons_SelectedIndexChanged"><asp:ListItem Text="Price" Value="productPrice"></asp:ListItem><asp:ListItem Text="Quantity " Value="Qty"></asp:ListItem></asp:RadioButtonList> My question is how will my stored procedure take in the radio button that has been clicked and order it by that value, e.g if price is clicked how will the stored procedure take taht in, this is how my normal unordered stored procedure currently looks; CREATE PROCEDURE stream_CategoryResults@subCategoryID INT ASSELECT DISTINCT Companies.companyName, Companies.companyLogo,SubCategories.subCategoryName, Products.productPrice, Products.productInfoURL FROM Companies INNER JOIN Products ON Companies.companyID = Products.companyID INNER JOIN SubCategories ON Products.subcategoryID = SubCategories.subCategoryID WHERE SubCategories.subCategoryID=@subCategoryID
View 8 Replies
View Related
Mar 31, 2004
Hey huys, I got this stored procedure. First of all: could this work?
--start :)
CREATE PROCEDURE USP_MactchUser
@domainUserID NVARCHAR(50) ,
@EmployeeID NVARCHAR(50) ,
@loginType bit = '0'
AS
INSERT INTO T_Login
(employeeID, loginType, domainUserID)
Values
(@EmployeeID, @loginType, @domainUserID)
GO
--end :)
then I got this VB.Net code in my ASP.net page....
---begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed
End Sub
---
If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?
View 2 Replies
View Related
Apr 1, 2004
I want to pass and array of ids to a procedure for inserting i a relation table.
I found some examples in other posts, but had problems getting them to work.
I just want to pass a parameter with value like '1,45,89' to the procedure, then loop through it to insert the relations.
(I´m using sql server 2000), had some problem with examples with strpos then.
Any hints ?
peace.
View 2 Replies
View Related
Nov 28, 2005
hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in
View 1 Replies
View Related
Jan 12, 2006
Hi, that's the problem:
I have a GridView, bound to a SQLDataSource, with an stored procedure as a Select query. The Select Parameters are bound to controls in the web form, acting like some filter fields.
When I submit the page, everythings works fine, except when I try to set some value in the DateTime fields. .Net is enclosing the date with extra single quotes, as I could see in the Profiler:
exec sel_despesa_procura @codigo=NULL,@fornecedor=NULL,@descricao=NULL,@vencto_ini=''2005-10-10 00:00:00:000'',@vencto_fim=''2005-10-20 00:00:00:000'',@pagto_ini=NULL,@pagto_fim=NULL,@valor=NULL,@valor_pago=NULL,@centro_custo=NULL,@pago=N'0,1'
The fields are defined as follows:
<SelectParameters>...<asp:ControlParameter ControlID="txtFiltroVencIni" Name="vencto_ini" PropertyName="Text" Type="DateTime" /><asp:ControlParameter ControlID="txtFiltroVencFim" Name="vencto_fim" PropertyName="Text" Type="DateTime" />...</SelectParameters>
The stored procedure doesn't even execute, due to the bad formatted arguments. It returns the error:
Msg 102, Level 15, State 1, Line 1Incorrect syntax near '2005'.
I'm going to change the parameter type to varchar, as a workaround, but I'd like to solve this problem.
Thanks in advance,
Anderson
View 1 Replies
View Related
Jan 18, 2006
I have the following stored procedure
ALTER PROCEDURE dbo.TextEntered
@searchString varchar(30)
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
Select GAVPrimaryKey, NAME from towns where NAME like @searchString order by NAME
RETURN
doesn't work!!!!
I would like to produce the following where s is @searchString:
Select GAVPrimaryKey, NAME from towns where NAME like 's%' order by NAME
Any ideas how I might acomplish this I have tried almost everything !
View 1 Replies
View Related
Apr 3, 2006
Hi All,I have sometimes used the following sort of query to pull data from one table to another:INSERT INTO Table1 SELECT fname, lname FROM Table2Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did something like this:EXECUTE Table1 _Insert SELECT fname, lname FROM Table2It won't work, giving an error that looks something like this:Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0Procedure 'Table1_Insert' expects parameter '@fname', which was not supplied.I assume I'm not doing things right... how would I pass a result set to a stored procedure, with each row corresponding to an input parameter of the stored procedure?
View 11 Replies
View Related
May 22, 2000
make it so that I compare that parameter, that is a char(3), to something like "XXX" and then choose the correct SELECT statement to use depending on that parameter.... much like an if else.
Any help is GREATLY appreciated. Thanks.
Robert
View 1 Replies
View Related
Feb 12, 2001
Hello, I'd greatly appreciated if someone can help me out on this problem:
I'd like to pass a string (which is a multiple value in this case - "Smith", "Lee", "Jones", "Hanson") as an input parameter to a stored procedure. I'd like to use this string as part of the select statement:
exec sp_GetLN "smith, lee, jones, hanson"
In the stored procedure:
@strLN varchar <- "smith, lee, jones, hanson"
/*What do I need to do here to SET/REPLACE the original string so that the syntax can be accepted by the select statement??*/
SELECT * from tblCustomers
WHERE LASTNAME IN (@strLN)
It looks simple but I've been trying to get the syntax to work. What can I do to change the quote from ' to "?? Can I use char(34) like VB??
Your help is greatly appreciated~
Thank you
View 2 Replies
View Related
Jun 8, 2000
Is it possible to have a stored procedure that passes recordsets which are created and passed from different tables and joins the information in those recordsets to form one group of information?
View 1 Replies
View Related
Jul 28, 2004
Hi all,
I want to use a stored procedure to access data in different databases. I was wondering if it is possible to pass the database name to the stored procedure as a parameter and use it in the sql statement.
Thank you for all the help you can give me.
Regards,
Eve
View 3 Replies
View Related
Mar 17, 1999
I am using VB6 and SQL Server 6.5. I want to pass a boolean to a SQL Server stored procedure. The code below is the main part that tries to pass 'True' to the stored procedure, but an error occurs when the line rst.Open.... is reached ? The error is as follows: The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.
I have tried putting 'True' directly in the rst.Open... line instead of assigning it to a boolean variable but the same error occurs.
The stored procedure works fine when run in SQL Server.
Can anyone help ? Please.
complete = True
Set rst = New ADODB.Recordset
rst.CursorType = adOpenForwardOnly
rst.LockType = adLockReadOnly
rst.Open "usp_CompleteUID_sel (" & complete & ")", UIDCon, , , adCmdStoredProc
Do While Not rst.EOF
lst_pastUIDs.AddItem (rst.Fields(1))
rst.MoveNext
Loop
rst.Close
View 1 Replies
View Related
Mar 21, 2008
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?
View 4 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
Mar 21, 2008
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?
View 5 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 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
Aug 25, 2006
Hi,I have a stored procedure which expects a Null value if a user does not enter something into a TextBox. The query should return all rows if this case.The stored procedure works as expected in Query Analyzer, but my application does not seem to send a Null and no rows are returned. Any ideas?Thanks.ps I am using SqlDataSource and GridView, this is the first time I have tried to connect to a database with asp.net.
View 3 Replies
View Related
