When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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.


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
		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 (  )

	EXEC SP_getSomething 'param1', 'param2'


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:

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?


Read Other SQL Guru Questions

ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article