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

Read Other SQL Guru Questions