How to capture table info in nested stored sps

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 2000 Forums

SQL Server Development (2000)

How to capture table info in nested stored sps

Author

Topic

rkhosla
Starting Member

3 Posts

Posted - 2002-08-15 : 11:18:39

I have one stored procedure,say A, calling another stored procedure, say B. B has the following :
select b.FirstField , b.SecondField from b

I was wondering how can I capture this information in A??

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-15 : 14:45:39

Me too. Guess I'll have to buy Ken Henderson's other SQL Server book.

Shaner
Starting Member

9 Posts

Posted - 2002-08-15 : 15:06:44

You can try this...its clunky but it works.
-----------------------------------------------------------
EXEC sp_serveroption '<YoureServerName\Instance>', 'data access', 'true'

SELECT *
FROM OPENQUERY([<YoureServerName\Instance>],'{call sp_who}')
------------------------------------------------------------

The sp_serveroption just enables DATA ACCESS which will ne to be true in order to do this. You may need to execute sp_addlinkedserver as well as this uses distributed query logic to run. The "sp_who" can be replaced with any stored proc you want. The bad thing about this is that OPENQUERY() does not allow any variables as parameters!! :( HOW LAME IS THAT!!

You could also have procedure B store its results in a table datatype variable and pass the table back to the parent proc.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-15 : 15:14:57

I might be missing something, but can't you just modify the B procedure to insert the data into a global temp table or regular table?

CREATE PROCEDURE sp_B AS
SELECT b.FirstField, b.SecondField INTO ##results_B FROM B

You can then select from the ##results_B table in the A procedure. Or, modify A like this:

CREATE PROCEDURE sp_A AS
CREATE TABLE #B_results (FirstField varchar(20), SecondField varchar(20))
INSERT INTO #B_results EXECUTE sp_B
SELECT * FROM #B_results
DROP TABLE #B_results

And you CAN use parameters with OPENQUERY, if you construct the entire SELECT...FROM OPENQUERY... statement as dynamic SQL and then execute it. Works like a charm.

Shaner
Starting Member

9 Posts

Posted - 2002-08-15 : 15:48:23

Thanx Rob...yes...I have used open query that way before too(dynamic sql). But it sort of defeats the purpose I wanted to use it for in the first place. Which was to insert result sets from stored procs directly into a table data type variable withoout the overhead of temporary tables as well as being able to filter the result set from a stored proc with inserting into anything at all :)

Edited by - Shaner on 08/15/2002 15:49:26

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-16 : 05:21:56

quote:

INSERT ... EXECUTE

Thanks Rob! I can't imagine how many times I've managed to miss that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources