CODEWITHSUNDEEP
sql-serversql-server-2005stored-procedures
djangofan
djangofan

Reputation: 29669

Can the results of a stored procedure be sorted with T-SQL?

I am operating on a MSSQL 2005 database system that has many stored procedures. One of the stored procedures is a "report" and I want to sort it without editing the stored procedure. Is there a way to sort the results of a stored procedure on the fly : something like this:

exec spReport 
order by ColumnT

Upvotes: 4

Views: 6545

Answers (3)

EricZ
EricZ

Reputation: 6205

You can insert into a temporary table. Then, sort from table.

e.g.

INSERT INTO #table
EXEC spReport

SELECT *
FROM  #table
ORDER BY ColumnT

Upvotes: 2

platon
platon

Reputation: 5340

You should fill a temporary table with the result and then sort it. Here is the link showing how to do the first part:

How to SELECT * INTO temp table FROM Stored Procedure

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

No, you cannot do this. If you know the structure of the resultset, you can build a #tmp table first, insert #tmp exec spReport, then select from the #tmp table with an order by clause. You may even be able to hack something together using OPENQUERY or OPENROWSET, but I would recommend editing spReport either to always use the order you want or to take a parameter and define the order based on the parameter.

Upvotes: 1

Related Questions