sql-server-2008: how do i sort the results of a procedure?

Question

i am running:

exec sp_who 
go

is it possible for me to sort by a field like:

exec sp_who order by dbname
go

?

i would i accomplish this?

Answer 1

If possible, the best solution would be to try and refactor the procedure into a view.

EDIT

You could also modify the procedure to stick the output into a temporary table, which you could then query.

If you are only going to sort by dbname, the the simplest solution would be to modify the procedure to always sort by dbname.

Answer 2

You can accomplish this using OPENROWSET as per this question.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC sp_who')

SELECT * FROM #MyTempTable order by [dbname]

If for whatever reason you are unable to use OPENROWSET then you will have to create a temp table that matches the output of sp_who exactly.

e.g

Create Table #temptable
(
spid smallint,
ecid smallint,
...
...
)

Insert Into #temptable
Exec sp_who

Select * From #temptable order by [dbname]

Answer 3

Run:

exec sp_helptext sp_who

This will give you the SQL to use. Copy this into a new query window and add in your ORDER BY clause.