CODEWITHSUNDEEP
sqlsql-server-2008
Alex Gordon
Alex Gordon

Reputation: 60691

Sorting the results of a procedure in SQL Server 2008

I am executing:

exec sp_who 
go

Is it possible to sort by a field like this?

exec sp_who order by dbname
go

How would I accomplish this?

Upvotes: 3

Views: 4645

Answers (3)

codingbadger
codingbadger

Reputation: 43974

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,
status varchar(100),
loginame varchar(100),
hostname varchar(100),
blk smallint,
dbname varchar(100),
cmd varchar(100),
request_id smallint
)

Insert Into #temptable
Exec sp_who

Select * From #temptable order by [dbname]

Upvotes: 1

Neil Knight
Neil Knight

Reputation: 48537

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.

Upvotes: 1

mikerobi
mikerobi

Reputation: 20878

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.

Upvotes: 1

Related Questions