December 4, 2013 at 1:57 pm
I have a stored procedure where one of the input parameters is an "order by" string. The last statement of the stored procedure is:
exec ('select * from #Result order by ' + @OrderBy)
This works fine, but I want to replace the temp table with a table variable. I no longer can use the exec statement as the table variable is outside the scope of the exec statement. I've seen articles where people use a case statement, but that's not an option in my case. I thought of using CTE with row_number(), but the @OrderBy string is not substituted. Here's the example I tried unsuccessfully, although it didn't give me any error. Help is appreciated.
declare @Result table
(
IndexID int identity(1,1),
ID int
)
declare @OrderBy varchar(100) = 'ID asc'
insert@Result ( ID )
values( 10 ),
( 21 ),
( 3 ),
( 14 ),
( 35 ),
( 26 )
;with results as
(
select*, ROW_NUMBER() OVER (ORDER BY @OrderBy) AS RowNum
from@Result
)
selectIndexID, ID
fromresults
order by RowNum
Result:
IndexIDID
110
221
33
414
535
626
December 4, 2013 at 2:29 pm
Technically speaking your Row_Number is in fact ordering, it is just ordering by the constant value of the parameter. 😉
Be very very careful with what you are doing here. So far everything you described and are trying to do it vulnerable to sql injection.
I would think that something like a case expression should work.
...
order by case @SortOrder when 1 then 'ID asc' when 2 then 'SomeOtherColumn desc' end
If you have any other parameters you MUST be very careful to never never execute those parameters directly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 4, 2013 at 3:13 pm
I wanted to avoid the case statement. I also looked into creating table types and using sp_executesql. But that defeats the purpose of the table variable because I now would have to manage different types for different stored procedures. With table variables or temp tables, this isn't a problem because I could use the same table variable name, @Result, in all my stored procedures.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply