Select stmt on a SP record set

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select stmt on a SP record set

Author

Topic

de9625
Starting Member

17 Posts

Posted - 2003-05-05 : 07:18:10

What I want to do is something like this:

Select field1 , filed1
From (exec input1,input2)
Where input1 = x
Order by input2 desc

I suppose that this can be done by a temp table or a cursor...but I dont know where to start.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-05 : 07:29:38

You can convert the sp into a udf.
http://www.sqlteam.com/item.asp?ItemID=1955

Otherwise if you're calling an existing sp you'll have to insert the results into a temp table.

CREATE TABLE #TempTable (field1 type1,....,fieldx typex)
INSERT INTO #TempTable
EXEC sp_mystoredproc input1, input2

SELECT field1, field2
FROM #TempTable
WHERE input1 = x
ORDER BY input2 DESC

DROP TABLE #TempTable

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-05-05 : 07:30:51

You can stick the results in a temp table or you can use OPENQUERY to a loopback linked server e.g.

 
select ProductName,Total from 
OPENQUERY(LOOPBACK,'exec Northwind.dbo.CustOrderHist ''ALFKI''')
where Total>20
order by ProductName

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources