Select Statements and Nested Stored Procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Statements and Nested Stored Procedures

Author

Topic

thedryden
Starting Member

23 Posts

Posted - 2008-03-21 : 16:29:46

I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.

Is there any way to do either of those?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-21 : 16:49:50

There are two ways that I know how to do this; with OpenRowset or with an EXEC:

-- OpenRowSet 
-- You do not need to know the returning table structure, 
-- but you may need to setup "Ad Hoc Distributed Queries"
SELECT  
	* 
INTO 
	#TempSpWho
FROM    
	OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who') AS T

SELECT * FROM #TempSpWho 


-- Exec method
-- Need to know and create the destination table before calling the stored procedure

CREATE TABLE #MyTemp AS
-- put table definition here
...

SELECT *
INTO #MyTemp
EXEC('master.dbo.sp_who')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources