SQL: Sort results based on a Stored Procedure parameter

If you want to be able to specify the sort criteria for the results returned from a stored procedure, based on a provided parameter, you might want to try using the QUOTENAME function 1.


Firstly, here is the declaration (it can easily be changed into a stored procedure):

Next, try the SELECT statement without using QUOTENAME:

You should receive the following error:

Msg 1008, Level 16, State 1, Line 4
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

Now, try using the QUOTENAME Function: (you have to use a variable to build the SQL statement)

This should now work 🙂

If you want to see what the QUOTENAME function returns, try this:

The returned result should be:

  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

CJayMead's SQL Server, Endeca, MS Office Help © 2018