update based on semicolon seperated string param

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

update based on semicolon seperated string param

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-03-26 : 10:22:25

Hi

I need to pass multiple integers to a stored procedure that update a column based on each ID. For example, if I pass on 3 ID's like this...

1;2;3

UPDATE OrderRow SET IsFetched WHERE ID = xx

The above should be within a loop that update based on 1, 2 or 3.

Can someone please show me how to do this?

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-26 : 10:26:50

[code]
declare @vcID as int
set @vcID='1;2;3'
set @vcID= '(' + replace(@vcID, ';' , ',') + ')'

UPDATE OrderRow
SET IsFetched=1
WHERE ID in @vcID

[/code]

sabinWeb MCP

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-03-26 : 10:31:07

Thanks, but I get a Incorrect syntax error near '@vcID'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-03-26 : 10:33:14

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Too old to Rock'n'Roll too young to die.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-01 : 15:45:25

what you can do is have a function that splits the data you pass in and load that data to a temp table or table variable. After that join the temp table / table variable to the actual table and write the update statement. It will be more efficient and works as you need it that using an IN operator.

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-01 : 15:46:04

quote:
Originally posted by sqlsaga

what you can do is have a function that splits the data you pass in and load that data to a temp table or table variable. After that join the temp table / table variable to the actual table and write the update statement. It will be more efficient and works as you need it that using an IN operator.

Read an article about using a split function @ http://sqlsaga.com/sql-server/split-function-in-sql-server/

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources