Update a table from two comma separated parameter as input

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/14681422/update-a-table-from-two-comma-separated-parameter-as-input

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @TableName Table(ID INT, "Order" int);

INSERT INTO @TableName VALUES
( 1,           1),
( 2,           2),
( 3,           3),
( 4,          4);

DECLARE @sID VARCHAR(100) = '1,2,3,4';
DECLARE @sOrder VARCHAR(10) = '2,4,1,3';

DECLARE @sIDASXml xml = CONVERT(xml,
                            '<root><s>' + 
                            REPLACE(@sID, ',', '</s><s>') + 
                            '</s></root>');
DECLARE @sOrderASXml xml = CONVERT(xml,
                        '<root><s>' + 
                        REPLACE(@sOrder, ',', '</s><s>') + 
                        '</s></root>');

;WITH ParsedIDs
AS
(
    SELECT ID = T.c.value('.','varchar(20)'),
    ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowNumber
    FROM @sIDASXml.nodes('/root/s') T(c)
), ParsedOrders
AS
(
    SELECT "Order" = T.c.value('.','varchar(20)'),
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowNumber
    FROM @sOrderASXml.nodes('/root/s') T(c)
)
UPDATE t
SET t."Order" = p."Order"
FROM @tableName AS t
INNER JOIN
(
   SELECT i.ID, p."Order"
   FROM ParsedOrders p 
   INNER JOIN ParsedIDs i ON p.RowNumber = i.RowNumber
) AS p ON t.ID = p.ID;

SELECT * FROM @tablename;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results