September 12, 2003 at 11:39 pm
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?
Edited by - agarwalshailesh on 09/12/2003 11:49:28 PM
September 13, 2003 at 2:41 am
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 2:34 pm
Exactly. Unfortuneately it forces you into dynamic sql, with the associated perf hit and security consequences. One alternative (which may give you ideas about how to do the same thing a different way) is to pass your string in as an XML doc, then use OPENXML, so the in becomes a join instead (SQL2K only).
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply