using return value of one proc in another proc

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)

using return value of one proc in another proc

Author

Topic

sternaphile
Starting Member

5 Posts

Posted - 2007-07-03 : 02:34:56

It's late, I'm tired, I'm confused...

With that said, I have a StoredProc1 that calls another StoredProc2 and I need to use the return set from StoredProc2 in StoredProc1. Should be relatively simple but it's just not making sense right now. Below are the storedproc codes:

ALTER PROCEDURE [dbo].[sp_StoredProc1]
-- Add the parameters for the stored procedure here
@Symbol varchar(50)
AS
BEGIN
-- Insert statements for procedure here
declare @returnset as varchar(50)
exec @returnset = sp_StoredProc2 @Symbol
-- do some stuff with the result set from StoredProc2
END

...

ALTER PROCEDURE [dbo].[sp_StoredProc2]
-- Add the parameters for the stored procedure here
@Symbol varchar(50)
AS
BEGIN
-- Insert statements for procedure here
SELECT Top(1)* from table1 where DataID like @Symbol + '[0-9]%'
order by DataID DESC
END

...

Any tips on what I'm doing wrong in StoredProc1?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 02:38:25

That is not a proper way to retrieve result set from second stored proc. You need to create a temp table with the schema similar to the results returned by the second SP and insert the second SP's output to that temp table like this:

Insert into #temp
EXEC sp_StoredProc2 @Symbol

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 03:27:41

Or, if possible, rewrite SP to a table-value UDF.

Peter Larsson
Helsingborg, Sweden

sternaphile
Starting Member

5 Posts

Posted - 2007-07-03 : 11:49:13

Alright; I've changed StoredProc1 to create a temp table to handle the return of StoredProc2. Now, if I execute StroedProc2 I get a return set, but if I execute StoredProc1 with the same params, I get a result set of 0. I believe that the data is in the temp table, but I cannot access it in StoredProc1. How do I access the data contained in the temp table while I'm in StoredProc1?

ALTER PROCEDURE [dbo].[sp_StoredProc1]
-- Add the parameters for the stored procedure here
@Symbol varchar(50)
AS
BEGIN

create table #temp(OrderRefKey varchar(20), Quantity int, Price decimal(18,2), Status varchar(20), FillDataID varchar(50), TradeDate datetime)
Insert into #temp
exec sp_StoredProc2 @Symbol
END

...

ALTER PROCEDURE [dbo].[sp_StoredProc2]
-- Add the parameters for the stored procedure here
@Symbol varchar(50)
AS
BEGIN
-- Insert statements for procedure here
SELECT Top(1)* from table1 where DataID like @Symbol + '[0-9]%'
order by DataID DESC
END

...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 11:54:41

What do you mean you can't access data in temp table? What are you trying to do with temp table in SP1?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sternaphile
Starting Member

5 Posts

Posted - 2007-07-03 : 12:45:36

I'm trying to get back one of the fields in temp table returned by StoredProc2, do some stuff with the data in that field, and store that back to a different table.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-03 : 12:49:10

read this to understand the temp table hierarchy
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-07-03 : 17:02:12

Gotta agree with Peso. You'd save yourself a lot of trouble if you rolled this logic into UDFs.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources