T-SQL help - SQL Server Forums

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)

T-SQL help

Author

Topic

PHUser
Starting Member

11 Posts

Posted - 2010-03-10 : 12:56:50

I have a list of ids in a particular order. I am passing this as a comma-delimited string to a stored procedure. I need to update a table and the sort field according to the order in which the ids came in.

for eg.

SortTable
Id SortColumn
100 0
105 1
102 2
104 3
200 4

Now passing in the list of ids to stored proc
ids = '100,102,104,200,105'

Result after stored proc runs
SortTable
Id SortColumn
100 0
105 4
102 1
104 2
200 3

Can you please help me with the SQL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 13:11:43

[code]UPDATE t
SET t.SortColumn=f.ID-1
FROM YourTable t
JOIN dbo.ParseValues(@yourString,',')f
ON f.Val=t.Id
[/code]
dbo.ParseValues can be found below

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources