March 13, 2006 at 5:51 am
I have a table which contains Item Id and their corresponding States stored in a table. Now I need to write a stored procedure which can accept the Items’ Ids as a parameter (it can be more than one) and then update the states of those items in one go.
The structure of the States Table looks as follows:
Item Id State
101 0
102 2
103 5
104 3
105 6
So for e.g. if I want to change the states of items 101, 103 and 105 and make it 10, how can I do it in one shot? I am selecting the file names from a Listview in my Dot net interface and then I would call an SQL Statement to update the states of the selected files.
If anyone could help me in this one, I’d be more than grateful.
Thanks,
Ron.
March 13, 2006 at 6:10 am
Can the list of items be very large ? If not, then all this amounts to is an update statement with an IN clause for the Item values.
If the list can be very large and if there is no way to qualify those set of Items using some attribute, then you can pass in a comma separated list to the procedure and then use one of the methods shown on this site:
http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-integers
to simulate the array and do the update.
March 16, 2006 at 2:53 am
you should make the parameter input like this from the application code : '''101''','''103''','''105'''
then, create a stored procedure to update using "IN" clause..
create procedure procA (@inputParameter varchar(255))
as
begin
declare @STR varchar(255)
set @STR='update states
set state=99
where itemid in (' + @inputParameter + ')'
exec @STR
end
then execute this : exec procA '''101''','''103''','''105'''
it should be easier with that kind of input parameter
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply