1

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

BumbleBee
  • 10,429
  • 20
  • 78
  • 123

2 Answers2

2

  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.

Community
  • 1
  • 1
stakx - no longer contributing
  • 83,039
  • 20
  • 168
  • 268
2

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

order by charindex(a.num, @NumList)
scrounger
  • 48
  • 8