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.

Example:

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

DECLARE @OrderBy VARCHAR(50)
SET @OrderBy = 'Name'

Next, try the SELECT statement without using QUOTENAME:

SELECT TOP 10 * FROM dbo.Person
ORDER BY @OrderBy

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)

DECLARE @SQL VARCHAR(1000)

SET @SQL = 'SELECT TOP 10 * FROM dbo.Person '
SET @SQL = @SQL + 'WHERE ' + QUOTENAME(@OrderBy) + ' IS NOT NULL '
SET @SQL = @SQL + 'ORDER BY ' + QUOTENAME(@OrderBy) + ' ASC'
EXEC (@SQL)

This should now work 🙂

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

SELECT QUOTENAME(@OrderBy)

The returned result should be:

[Name]
  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 e-mail address will not be published. Required fields are marked *

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