Can I Use Order by to sort Stored Procedure results?

Question

Simply, I have this SQL statment:

EXEC xp_cmdshell 'tasklist'

can we order or filter the results by using order by or where?

Thanks,

You could add these as parameters to the sp: http://4guysfromrolla.com/webtech/010704-1.shtml — Tim Schmelter, Oct 26 '10 at 12:53
No it's not Duplicated, I want tSQL Code not in DotNet platform. — Wael Dalloul, Oct 26 '10 at 13:03

score 23 · Answer 1 · answered Oct 26 '10 at 13:32

23

I checked jamietre link, and this is the complete answer:

Create table  #MyTempTable
(output varchar(max))

INSERT INTO #MyTempTable
EXEC xp_cmdshell 'tasklist' 

select * from #MyTempTable where output like 'ie%' order by output

Thanks for all...

answered Oct 26 '10 at 13:32

Wael Dalloul

22,172
11
48
57

score 17 · Accepted Answer · edited May 23 '17 at 12:02

17

You need to output the results into a temporary table first. This should show you how to do it

Insert results of a stored procedure into a temporary table

edited May 23 '17 at 12:02

Community

1
1

answered Oct 26 '10 at 12:53

Jamie Treworgy

23,934
8
76
119

score 3 · Answer 3 · answered Oct 26 '10 at 12:51

3

not directly. You can insert exec into a temp table or table variable and then sort that though

answered Oct 26 '10 at 12:51

chuck taylor

2,476
5
29
46

score 2 · Answer 4 · answered May 10 '21 at 08:17

When running the above query multiple times, you might run into this error: There is already an object named '#MyTempTable' in the database.

To mitigate this you can use a DROP IF EXISTS statement as follows before creating the temp table.

IF OBJECT_ID(N'tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyTempTable
END
CREATE TABLE #MyTempTable
(OUTPUT VARCHAR(max))

INSERT INTO #MyTempTable
EXEC xp_cmdshell 'tasklist' 

SELECT * FROM #MyTempTable WHERE OUTPUT like 'ie%' ORDER BY OUTPUT

Can I Use Order by to sort Stored Procedure results?

4 Answers4

Linked