CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Faiz
Faiz

Reputation: 79

VARIABLE IN ORDER BY IN STORED PROCEDURE

I am trying to put variable in order by clause in a stored procedure but its returning an error. the code is as follows;

SELECT ...
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.SQL_HANDLE) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE @where LIKE '%'+@search+'%'
ORDER BY CASE @ORDER

The error is;

'Msg 1008, Level 16, State 1, Procedure sp_ExecInfo2, Line 40
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.'

Upvotes: 4

Views: 12394

Answers (4)

Reza
Reza

Reputation: 19843

It is not possible to use a variable in order by clause, you need to make a string for your query and then execute it by sp_execute_sql

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT ... '+
'FROM sys.dm_exec_query_stats AS qs '+
'CROSS APPLY sys.dm_exec_sql_text(qs.SQL_HANDLE) AS st '+
'CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp '+
'WHERE '+@where+' LIKE ''%'+@search+'%'''+
'ORDER BY '+@order

EXEC sp_executesql @sql

Upvotes: 0

Tommy Snacks
Tommy Snacks

Reputation: 171

You could potentially do something like this in the ORDER BY clause, but I think you are still going to have some issues with your WHERE clause.

ORDER BY 
        CASE @ORDER
            WHEN 'columnname1' THEN columnname1
            WHEN 'columnname2' THEN columnname2
        END

Upvotes: 1

Alex K.
Alex K.

Reputation: 175766

For a small set of candidate orders;

ORDER BY 
  CASE @ORDER WHEN 1 THEN fld1 END,
  CASE @ORDER WHEN 2 THEN fld2 END,
  CASE @ORDER WHEN 3 THEN fld3 END

Upvotes: 5

jean
jean

Reputation: 4350

As Reza said you cannot acomplish it this way. One way can be dynamic SQL but there are other alternatives. Also dynamic SQL have some pitfalls and you must plan a bit before going down with it (also be aware of SQL inject attacks!).

You can follow this link to a great and extensive article about a variety of thecniques about conditional SQL (conditional ordering included)

Dynamic Conditional SQL

Upvotes: 0

Related Questions