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
