Update table in SQL Server from two comma separated string parameter

Question

Possible Duplicate:
Update a table from two comma separated parameter as input

I have a Gridview in front end where Grid have two columns : ID and Order like this:

 ID        Order

 1           2
 2           4
 3           1
 4           3

Order column is editable. Now if I want to update the order and make save I want to store it into database. I have stored ID and Order as a comma separated string like sID(1,2,3,4) and sOrder(2,4,1,3) and sent to SQL Server as input parameters. Through Stored procedure how can update into the table.

Answer 1

you can create a Function that your Stored Proc can call to split the input parameters.

Refer to: How do I split a string so I can access item x?

Answer 2

Sources from various sites to come up with this:

DECLARE @sID nvarchar(max) = '1,2,3,4'
DECLARE @sOrder nvarchar(max) = '2,4,1,3'

DECLARE @Split char(1) = ','
DECLARE @xSID xml
DECLARE @xOrder xml

SELECT @xSID = CONVERT(xml,'<root><s>' + REPLACE(@sID, @Split,'</s><s>') + '</s></root>')
SELECT @xOrder = CONVERT(xml,'<root><s>' + REPLACE(@sOrder, @Split,'</s><s>') + '</s></root>')

SELECT [Value] = T.c.value('.','varchar(20)') FROM @xSID.nodes('/root/s') T(c) 
SELECT [Value] = T.c.value('.','varchar(20)') FROM @xOrder.nodes('/root/s') T(c)

replace the @sID and @sOrder as parameters for you SP.

Must add that this site pretty much answered the question: Social MSDN