T-SQL help

Question

T-SQL help

phbdev

Old Hand

Points: 376
More actions
March 10, 2010 at 10:55 am

#217517

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?

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

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

Jeff Moden SSC Guru Points: 1004447 More actions · Answer 1

preetshari (3/10/2010)
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?

Sure... can you post your stored procedure, please?

--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)

Paul White SSC Guru Points: 150468 More actions · Answer 2

No need for a Tally table here:

Setup

DECLARE @SortTable

TABLE (

id INTEGER NOT NULL,

sort_column INTEGER NOT NULL

);

INSERT @SortTable (id, sort_column) VALUES (100, 0);

INSERT @SortTable (id, sort_column) VALUES (105, 1);

INSERT @SortTable (id, sort_column) VALUES (102, 2);

INSERT @SortTable (id, sort_column) VALUES (104, 3);

INSERT @SortTable (id, sort_column) VALUES (200, 4);

Solution:

DECLARE @SortString VARCHAR(8000);

SET @SortString = '100,102,104,200,105';

UPDATE ReIndexed

SET sort_column = new_sort_column - 1

FROM (

SELECT ST.id,

ST.sort_column,

new_sort_column =

ROW_NUMBER()

OVER(

ORDER BY

CHARINDEX(

CONVERT(VARCHAR(12), ST.id)

COLLATE LATIN1_GENERAL_BIN,

@SortString))

FROM @SortTable ST

) Reindexed;

-- Show the results

SELECT id, sort_column

FROM @SortTable;

Output

id sort_column

100 0

105 4

102 1

104 2

200 3

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004447 More actions · Answer 3

That kind of presupposes that you know what order the delimited parameter is going to be in, doesn't it? 😀

--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)

Paul White SSC Guru Points: 150468 More actions · Answer 4

Jeff Moden (3/10/2010)
That kind of presupposes that you know what order the delimited parameter is going to be in, doesn't it? 😀

Nope. My reading of the requirement is that the order of keys in the string defines the ordering in the table. That's what my code does. Have I misunderstood?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

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

Paul White (3/10/2010)
Jeff Moden (3/10/2010)
That kind of presupposes that you know what order the delimited parameter is going to be in, doesn't it? 😀
Nope. My reading of the requirement is that the order of keys in the string defines the ordering in the table. That's what my code does. Have I misunderstood?

Not from what you said above. I guess I don't understand the purpose of @SortTable in your code. What if the passed in parameter has a value that's not in @SortTable? Maybe I'm the one misunderstanding.

--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)

Jeff Moden SSC Guru Points: 1004447 More actions · Answer 6

Heh... never mind. I didn't read the original post carefully enough.

--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)

Paul White SSC Guru Points: 150468 More actions · Answer 7

Jeff Moden (3/10/2010)
Heh... never mind. I didn't read the original post carefully enough.

No worries. It is a bit off an odd requirement... kinda makes me doubt that the system in question is a shining example of correct design.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi