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

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
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.

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


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