TO UPDATE THE VALUES IN ONE SHOT

Question

TO UPDATE THE VALUES IN ONE SHOT

Ronvy

Mr or Mrs. 500

Points: 597
More actions
March 13, 2006 at 5:52 am

#93440

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

There is a possibility to enter the item ID's as a delimited list (VARCHAR data type). Then inside the stored procedure you can parse the values, insert them into a temporary table or table variable, and run the update while joining this table to States table. It has been discussed numerous times on this forum. Just go to Search, enter "parse parameter list" and you'll find some of them... IMHO this will be better than to describe it here again, since you can choose the best from the various solutions posted.

Also, very good advice can be found on http://www.sommarskog.se/arrays-in-sql.html

HTH, Vladan

Steve Fine Ten Centuries Points: 1164 More actions · Answer 2

May be try something like this.

In as stored procedure, set pass in @ItemIDs = '101,103,105'

declare @Itemids varchar(1000)

declare @sql varchar(8000)

set @sql = 'Update tablename Set State = 10 where Item Id in(''' + @itemIDs + ''')'

exec(@sql)

Jerry Zornes SSC Rookie Points: 47 More actions · Answer 3

Jerry Zornes

SSC Rookie

Points: 47

March 14, 2006 at 8:38 am

#626548

That looked like a homework assignment

JacekO SSCertifiable Points: 6610 More actions · Answer 4

DECLARE @ID_list VARCHAR(100)

SET @ID_list = '100,106,103'

UPDATE tablename

SET

State = 10

WHERE CHARINDEX(CAST(ItemID AS CHAR(3)), @ID_list) > 0

The problem with this approach is that it is only going to work if the length of the Item ID is always 3 ( so your item ID is between 100 and 999). Otherwise you have to do something like this

DECLARE @ID_list VARCHAR(100)

SET @ID_list = ',1,106,10,' --include comma before the first one and after the last one

UPDATE tablename

SET

State = 10

WHERE CHARINDEX(',' + CAST(ItemID AS VARCHAR) +',', @ID_list) > 0

PS. The assumption was made that ItemID is INT if for whatever reason you have it as CHAR or VARCHAR then you don't have to CAST.

The problem with Steve's solution is that when you dynamically build and execute the SP you running the risk of syntax error that can not be detected during compilation and is introduced at runtime.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

http://forums.belution.com/en/sql/000/110/02.shtml

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ronvy Mr or Mrs. 500 Points: 597 More actions · Answer 6

Yea Jeff, I'm working on it. I has posted the reply there but missed out post one here.

Thanks anyways.

--Ron.

Ronvy Mr or Mrs. 500 Points: 597 More actions · Answer 7

Yea Jeff, I'm working on it. I have posted the reply there but missed out to post one here.

Thanks anyways.

--Ron.