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 1Please ensure you test any script taken from my website on a test/development machine, before running on a production server.
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:
The returned result should be: