CODEWITHSUNDEEP
sqlsql-servert-sqldynamicsql-order-by
Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

SQL. How to use ORDER BY clause while executing sp_executesql?

I need to use ORDER BY clause while executing sp_executesql.

I've tried following, but unsuccessfully:

1.

EXEC Sp_executesql @query + 'ORDER BY myChoice', N'@param1 TINYINT, @param2 NVARCHAR(60)', @param1, @param2

2.

EXEC Sp_executesql @query ORDER BY myChoice, N'@param1 TINYINT, @param2 NVARCHAR(60)', @param1, @param2

3.

EXEC Sp_executesql @query, N'@param1 TINYINT, @param2 NVARCHAR(60)', @param1, @param2 + 'ORDER BY myChoice'

Have you ideas? My syntax is incorrect.

Upvotes: 1

Views: 2420

Answers (2)

David Dubois
David Dubois

Reputation: 3932

sp_executesql does not allow the statement being executed to be an expression. It must be either a constant or a variable. No concatenation allowed.

One workaround would be to create another variable to hold the concatenated string.

declare @query2 nvarchar(1000)

set @query2 = @query + N' order by mychoice'

execute sp_executesql @query2, ....

Upvotes: 5

Rhys Jones
Rhys Jones

Reputation: 5508

You can't apply ORDER BY to an SP, but you can capture the output and apply the ORDER BY to that. This sort of thing;

declare @results as table (col1 int, col2 int) 
insert into @results execute sp_executesql @query, N'@param1 tinyint, @param2 nvarchar(60)', @param1, @param2
select * from @results order by myChoice

You will obviously have to define the @results table variable to reflect your data structure.

Upvotes: 2

Related Questions