Multiple result sets from stored procedure

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)

Multiple result sets from stored procedure

Author

Topic

SuperGhost
Starting Member

12 Posts

Posted - 2008-02-07 : 02:38:18

I have two stored procedures. One SP queries certain data and then I have a second SP to query additional data from the first SP.

Example:

#1: Parent

SELECT [...] FROM [etc]
SELECT @@rowcount

#2: Child

CREATE TABLE #temp_table { ... }
INSERT INTO #temp_table EXEC parent [etc]

I then receive the error: "Insert Error: Column name or number of supplied values does not match table definition."

I'm sure it's because of the second SELECT in the parent SP. Is there a way to get around this?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-07 : 02:58:18

When you are using INSERT EXEC you need to make sure the SP returns only fields that are present in INSERT table. I'm not sure why you are using SELECT @@rowcount. You need to remove it if you are planning to dump results of first SP to second.
Alternatively, if you want both, what you can do is to create a temp table in Parent (#temp) and insert into it the results of first select and you can use this temp table in Child SP also.

SuperGhost
Starting Member

12 Posts

Posted - 2008-02-07 : 10:54:42

Thanks visakh16,

I'm interested in the alternate method you described above but I am not certain of a few things.

1. In the parent SP I could still select 2 result sets, 1 from the temp table and 1 for the row count, and still use the parent SP in my child SP correct?
2. How would I refer to the temp table from my child SP?

Thanks for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-07 : 11:11:55

what i suggested was :-

#1: Parent
CREATE TABLE #temp_table { ... }
INSERT INTO #temp_table EXEC parent [etc]
SELECT [...] FROM [etc]

SELECT @@rowcount

#2: Child

SELECT * FROM #temp_table (wherever you want data from 1st SP's SELECT just call the temp table created there as you have already populated reqd data into it).

SuperGhost
Starting Member

12 Posts

Posted - 2008-02-07 : 13:51:08

visakh16, what happens if the stored procedures need to run independently? For example, I may call the Parent SP now and then a minute later call the Child SP. However what if another user comes in and calls the Parent SP... now my temp table contains new information instead of the first user's information.

That's why I was originally using "INSERT INTO [] EXEC Parent []" with the same parameters in my Child SP. It worked fine at the time but then I had a requirement to return the total number of rows from the Parent query. That's where the problem came in because of the second select statement.

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-07 : 14:31:20

This should do it for you.

Create Procedure SP1
As Begin
Select name From sysobjects
Where xtype = 'U'

Return @@rowcount
End

Create Procedure SP2
As Begin
Declare @result int

Declare @table Table (name varchar(50))
Insert Into @table Exec @result = SP1

Select @result, * From @table
End

Exec SP1
Exec SP2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 00:47:49

quote:
Originally posted by SuperGhost

visakh16, what happens if the stored procedures need to run independently? For example, I may call the Parent SP now and then a minute later call the Child SP. However what if another user comes in and calls the Parent SP... now my temp table contains new information instead of the first user's information.

That's why I was originally using "INSERT INTO [] EXEC Parent []" with the same parameters in my Child SP. It worked fine at the time but then I had a requirement to return the total number of rows from the Parent query. That's where the problem came in because of the second select statement.

Sorry there was a smal error in my last post. what i meant was :-

#1: Parent
CREATE TABLE #temp_table { ... }
INSERT INTO #temp_table [etc]
SELECT [...] FROM [etc]

SELECT @@rowcount

#2: Child
EXEC parent --once you call this temp table will be opulated and you --get @@ROWCOUNT returned
SELECT * FROM #temp_table--you can call temp table wherever you want like this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources