Return Table from Stored Proc - SQL Server Forums

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

Transact-SQL (2000)

Return Table from Stored Proc

Author

Topic

Sschuster
Starting Member

26 Posts

Posted - 2006-04-11 : 15:06:35

I am calling a Stored procedure, from a stored procedure, that will return basically a table. What I want to get at is the sum of one of the columns.

Ex.
StoredProcA calls StoredProcB. StoredProcB returns a group of records and StoredProcA needs to return the sum of one of the columns.

I have both procs working but need to know how to sum the results of a stored proc from within the calling stored proc.

Can anyone tell me how to do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-11 : 15:13:42

Put the results of the stored procedure into a table like this

INSERT INTO ...
EXEC dbo.StoredProd

Then sum from the table.

Tara Kizer
aka tduggan

Sschuster
Starting Member

26 Posts

Posted - 2006-04-11 : 15:24:44

OK. SO this is what I have now...

Begin
Insert INTO #Positions exec SP_MyStoredProc 'Param1'
End

Select @Sum = ISNULL(Sum(#Positions.Profit),0)
From #Positions

@Sum is declared as an output parameter. The error I get now is that #Positions is an invalid object name. Any suggestions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-11 : 15:27:33

Did you create the #Positions table?

Tara Kizer
aka tduggan

Sschuster
Starting Member

26 Posts

Posted - 2006-04-11 : 15:41:12

Do I need to physically create that table? I thought you were referring to a temp table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-11 : 15:44:34

Yes you have to create the table first, whether it be a temporary table or a real table. The only way for SQL Server to create it no its own is through SELECT INTO, which is not available for this. So you must create it first prior to inserting.

Tara Kizer
aka tduggan

Sschuster
Starting Member

26 Posts

Posted - 2006-04-11 : 15:52:21

Is there a streamlined way of creating this table based on the results of the second stored proc or do I need to use the Create Table method and list out each column explicitly?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-11 : 15:57:22

You must use the CREATE TABLE statement and list out each column explicitly.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources