CODEWITHSUNDEEP
t-sqlsql-server-2012
BumbleBee
BumbleBee

Reputation: 10789

Returning the results in the 'SAME ORDER' as the input param

In the below query what should I do to get the results in the same order as the input param. 

DECLARE @sql varchar(max)
SET @sql = 'SELECT a.num AS Num, a.photo as Photo , row_number() over (order by (select 0)) rn
            FROM tbl a (nolock) WHERE a.num IN (' + @NumList + ') '

I pass in to the @NumList param the following (as an example):

1-235,1-892,2-847,1-479,3-890,1-239,2-892

This works fine, however I need the results returning in the 'SAME ORDER' as the input param.

I have created a SQL Fiddle

Upvotes: 1

Views: 184

Answers (2)

scrounger
scrounger

Reputation: 48

If @NumList contains unique values you could use CharIndex to find their position within the param ex:

order by charindex(a.num, @NumList)

Upvotes: 2

stakx - no longer contributing
stakx - no longer contributing

Reputation: 84765

  1. Create a local temporary table #numbers. Ensure that it has an auto-increment identity column.

  2. Insert the numbers from @NumList into #numbers in the correct order.

    • Split @NumList at the commas and turn the individual values into rows. See e.g. this question for ideas how to do this.
    • Alternatively, turn @NumList from a VARCHAR-typed variable into a table variable. (That way, you might even be able to use it directly, i.e. in place of #numbers.)
       

  3. Modify your query so that rows from tbl a are joined to #numbers. Also, add an ORDER BY clause that sorts the result by the auto-increment identity column of #numbers.

Upvotes: 2

Related Questions