September 13, 2003 at 1:36 am
I have a stored procedure which accepts three parameters, the first and the last parameter being a date , and the second parameter being a string. Now the problem is the value which i want to pass as the second parameter. i have a variable where i am storing this value, the data will be something of this format
strdata = "'a','b','c','d'"
i call my stored procedure as
execute procname datevar1, strdata, datevar2
the second parameter of my stored procedure takes a list of item codes seperated by comma. Now if i have selected only one item and pass it to the stored procedure, the procedure works fine. but if i pass two parameters it does not affect any records in the database, where it is suppose to do.
I am pasting a part of the code of the stored procedure to explain more clearly. This code works fine if i run it through Query analyzer but does not work in Stored Procedure (when i have more than one value passed as the second parameter to the stored procedure).
insert into TableName(Ldate, itemid, particulars, ref,ref2,qty)
Select @vsdate, itemid, 'Particulars', '','', stock from TableName2 where itemid in ( @itemids )
Can anyone suggest where the problem might be?
September 13, 2003 at 2:42 am
duplicate post .. but here's my answer again!
hi,
i think the problems is that the itemid in ( @itemids ) bit of the query is being resolved as ''a','b','c','d''.
a way around this would be to generate the select dynamically :-
exec('select * from TableName2 where itemid in (' + @itemids + ')')
this (hopefully) should expand @itemids in the way that you want
HTH
Paul
September 13, 2003 at 8:59 am
You could also use charindex to obtain results in this case...
Assuming that the table structure is something like
TableName(Itemid int,Itemname varchar(20))
Declare @itemids varchar(20)
SET @itemids = '1,2,3,4'
SET @itemids = ','+@itemids+','
Select * from TableName where
Charindex(','+Convert(varchar(10),itemid)+',',@itemids)>0
September 13, 2003 at 10:49 am
charindex is my vote. IN doesn't work with string variables.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 14, 2003 at 11:10 pm
Thanks for your reply, What i did was took the query into a variable and printed that query using Print Command.
When i tried to run the query individually it worked, let me paste my code below for your reference
CREATE PROCEDURE sp_abc(@vSdate datetime, @itemids as varchar(8000), @vedate datetime ) AS
begin
print @itemids
declare @itemid char(10)
declare @qty float
Delete From Stock
declare @testvar varchar(8000)
set @testvar="Select @vSdate ,itemid,'Opening Balance','','',stock from opinventory where itemid in (" + @itemids + ")"
print @testvar
insert into Stock(Ldate, itemid, particulars, ref,ref2,qty)
Select @vsdate, itemid, 'Opening Balance', '','', stock from opinventory where itemid in ( @itemids )
return
end
when i am calling the procedure as follows
sp_abc '05/sep/2003',"'I000001364','I000001372'",'15/Sep/2003'
it does not work but if i have only one item code in the second parameter it works
can you please suggest how to modify my code
September 15, 2003 at 12:07 am
Hi Shailesh,
As Steve Jones said - the IN doesn't work properly with string variables...
Consider the following :
SET NOCOUNT ON
create table #Test1(ItemId Varchar(10))
Insert #Test1(ItemId) Values('a')
Insert #Test1(ItemId) Values('b')
Insert #Test1(ItemId) Values('c')
Insert #Test1(ItemId) Values('d')
Declare @val Varchar(10)
SET @val = 'a'
-- this works
Select * from #Test1 where ItemId in (@Val)
SET @val = 'a,b'
-- this does not work
Select * from #Test1 where ItemId in (@Val)
drop table #Test1
SET NOCOUNT OFF
when you pass more than one variables delimited by a comma then you will either have to use dynamic Sql (as Paul suggested) or follow the charindex method...
using dynamic Sql in the above example :
Declare @STR Varchar(100)
SET @val = '''a'',''b'''
SET @STR = 'Select * from #Test1 where ItemId in ('+@Val+')'
Exec(@Str)
however dynamic Sql has associated security risks and performance problems...
using the charindex approach in the above example :
SET @val = 'a,d'
SET @val = ','+@Val+','
Select * from #Test1 where Charindex(','+ItemId+',',@Val) > 0
you could try the following changes in your stored procedure :
CREATE PROCEDURE sp_abc(@vSdate datetime, @itemids as varchar(8000), @vedate datetime ) AS
begin
SET @itemids = ','+@itemids+','
-- wrap the itemids within commas
print @itemids
Delete From Stock
insert into Stock(Ldate, itemid, particulars, ref,ref2,qty)
Select @vsdate, itemid, 'Opening Balance', '','', stock from opinventory where
Charindex(','+itemid+',',@itemids) > 0
return
end
and call the stored procedure as :
sp_abc '05/sep/2003','I000001364,I000001372','15/Sep/2003'
Let us know if this solves the problem...
Edited by - winash on 09/15/2003 12:34:25 AM
September 15, 2003 at 12:51 am
Thanks for your reply,
But, if i run the query in query analyzer it works
select * from test1 where itemid in ('I000010','I000020') it works, however when the same statment is executed within the stored procedure it does not work
September 15, 2003 at 1:03 am
true...the SQL statement that you are assigning to a variable and printing out will run in query analyser...to get that to work within your stored procedure you need to use dynamic sql...
set @testvar="Select @vSdate ,itemid,'Opening Balance','','',stock from opinventory where itemid in (" + @itemids + ")"
print @testvar
exec (@testvar)
and this should execute the query within your stored procedure...
and you could use :
insert into Stock(Ldate, itemid, particulars, ref,ref2,qty)
exec (@testvar)
HTH...
September 15, 2003 at 1:08 am
thanks it works,
but now i have another question
What if i would like to declare a cursor in stored procedure with a query which is stored in the variable
What is the syntax?
September 15, 2003 at 1:35 am
w.r.to the original stored procedure query --I'd try to avoid the dynamic Sql solution and go with the charindex approach...that would perform better IMO...
I've learnt other approaches in this forum (like the charindex one) and I don't think a cursor plus dynamic Sql mix very well (I think you'd have to put everything from the cursor declaration and execution to local variables used in the cursor FETCH statements into the dynamic Sql string)...
if you could post the requirement here then I'm sure someone could come up with solutions on avoiding the dynamic Sql and maybe even a set based approach instead of a cursor...
-----------------------------------------
When everything is coming your way......you're in the wrong lane
September 15, 2003 at 10:42 pm
Ok, I manage to work out most of the things with the dynamic sql way. but there is another problem.
The stored procedure is defined as
CREATE PROCEDURE sp_abc(@vSdate datetime,@itemids as varchar(8000) ,@vedate datetime ) AS
begin
..
..
end
I am calling my procedure as
sp_abc '14/Sep/2003',"'I000003645', 'I000003646', 'I000003647'",'16/Sep/2003'
This works fine now, but if i have too many items in the second parameter it gives me the following error
The identifier that starts with ''I000003645', 'I000003646', 'I000003647', 'I000003734', 'I000003735', 'I000001545', 'I000001546', 'I000001547', 'I000001553', 'I' is too long. Maximum length is 128.
How to solve this problem?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply