4

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
djangofan
  • 28,471
  • 61
  • 196
  • 289

3 Answers3

2

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

e.g.

INSERT INTO #table
EXEC spReport

SELECT *
FROM  #table
ORDER BY ColumnT
EricZ
  • 6,065
  • 1
  • 30
  • 30
  • This will only work if the proc always returns the same columns (and you will need a create table statement). If it does not then look at @Platon's link. – HLGEM Jul 18 '11 at 19:22
  • This doesnt work, as shown here: http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure – djangofan Jul 18 '11 at 20:06
  • @djangofan, did you try it? why you said it's not working? I agree that in this way, you can not do select into, but it more transparent than OPENROWSET. At least, if stored procedure return same column set, I will use INSERT INTO EXEC not OPENROWSET – EricZ Jul 19 '11 at 13:16
1

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
1

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

Community
  • 1
  • 1
platon
  • 5,310
  • 1
  • 22
  • 24