October 2, 2002 at 3:45 am
Hi!
I am wondering if there is a way of using a stored procedure from the "FROM" clause? What I mean is:
SELECT * FROM <stored procedure>
How should I go about this? I'm using SQL7.
Your help will greatly be appreciated.
Rgds,
Wally
October 2, 2002 at 3:54 am
What is in the Stored Procedure? Is it another Select statement?
You can't SELECT from a SP as in your example. However, if the SP contains another SELECT statement, then you can use a sub-query, like this...
SELECT FstName, LastName, Age
FROM Employees
WHERE CompanyName IN (SELECT DISINCT CompanyName FROM Companies WHERE County = 'Surrey')
This would select all rows from the employees table that worked in companies based in the county of 'Surrey'
If this is not what you are after, could you post your SP to give us a better idea of your needs.
Clive Strong
October 2, 2002 at 4:00 am
Hi Clive!
Thks for your reply. 🙂
Let's say the SP involved has some processing inside (to give out a result set of more than 10 rows with 3 columns) and not just another SELECT stmt.
Is it possible?!?
Again, thks alot!
Rgds,
Wally
October 2, 2002 at 4:46 am
What kind of processing are you doing in your SP? Not quite sure what to recommend...basically, if all your SP is doing is a complex SELECT statement, then you could use temp tables or derived tables. Derived tables being the better choice.
Clive Strong
October 2, 2002 at 6:11 am
Interesting, there could be several answers to your question depending on what exactly you're tryin to do
Would it be possible for you to post up an example of the kind of stored procedure code you envisage, then things might be clearer.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
October 3, 2002 at 7:35 am
There are basically two ways to process data from procedure:
SELECT A.*
FROM OPENROWSET('SQLOLEDB','HBDWDB1';'MON2ITOR';'MON2ITOR',
'EXEC CAPMANUAT.DBO.spname ') AS A
or
drop table testt
CREATe TABLE testt(
name varchar (200),
rows varchar (200),
reserved varchar (200),
data varchar (200),
index_size varchar (200),
unused varchar (200)
)
GO
INSERT INTO testt
EXEC sp_MSforeachtable "sp_spaceused '?'"
GO
Cheers,
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
October 3, 2002 at 6:12 pm
Note: on the openrowset or even a linked server using openquery back to itself has some major flaws. One I believe you have to turn on Data Access for those connections and this can create a security hole (sorry I forget the details just remember reading about it when I tried this). Next when the code runs it creates a new connection to the server itself that exists only in the scope of the calling connection. When done it takes about 30 secs to 30 minutes to drop and requires extra licensing if you are on a Per Seat CAL for SQL 7. Plus this extra connection does add some overhead to the servers memory, so the more folks you have running at the same time the greater risk you have of NIC and Memory bottlenecks.
Just an FYI.
Normally you just assume you cannot call an SP inline in a select statement and look for an alternate solution such as temp tables and cursors.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply