To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q013000-2.shtml

The SQL Guru Answers your Questions...


I'd like to do a select from a stored procedure, something like:

select something
from Execute("SP_getSomething 'param1', 'param2'")

Can this be done? I use SQL-server 6.5 and 7. I often use the sql-server to generate HTML-code, for example to generate tables. If I could use stored procedures for this, I could easily enforce business rules without my usual VBScript classes.

Thanks!

Unfortunately, no , but you can do a few similar things. First, you can use a derived table:

SELECT foo, bar
FROM (SELECT key, bar FROM t1) AS Derived
	JOIN T2
		ON Derived.key = T2.key
...

Basically what you're doing here is using a sub-select in the FROM clause. You can then treat the results of that statement as a table and join it to another table if needed. The example I have above isn't too complex, but it illustrates the point. Derived tables can help save a step where you would otherwise need to use a temp table. In fact, there is a great article on 4Guys that discusses how to use dervied tables.

You can also INSERT into a table with the results of a SELECT. So, your code sample might look like this:

CREATE TABLE #temp (  )

INSERT #TEMP
	EXEC SP_getSomething 'param1', 'param2'

SELECT * FROM #temp

Another thing you could do is to set up a linked server that points back to the server on which that SP resides and use an OPENQUERY operator to perform a remote query:

SELECT * 
FROM OPENQUERY(LinkedServer,'SP_getSomething ''param1'', ''param2''')

All of these seem like a bit of a hack, though, based on the info I have... Couldn't you just have the SP return the appropriate resultset?

Sean


Article Information
Article Title: SQL Guru: SELECTing a Recordset from the Results of a Stored Procedure
Article Author: Scott Mitchell
Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q013000-2.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers