Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Call Another stored procedure which returns muliple rows Expand / Collapse
Author
Message
Posted Thursday, February 25, 2010 5:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 22, 2013 5:34 AM
Points: 58, Visits: 151
Hi,

I am using sql server 2005,In that we have one stored procedure A to call stored procedure B, which returns a set of records from a table. Then, I want stored procedure A to perform work on these records. How do I call stored procedure B from within stored procedure A?

Thanks in advance.
Post #872639
Posted Thursday, February 25, 2010 6:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 5,473, Visits: 23,560
Sorry do not understand your question ... why not reverse the calling order of your 2 procedures. That is call procedure "B" first to insert the data into either a temporary table or a table variable and then operate on that data with procedure "A".

If you follow the example given by clicking on the first link in my signature block you will most likely receive the assistance you are requesting.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #872644
Posted Thursday, February 25, 2010 6:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 22, 2013 5:34 AM
Points: 58, Visits: 151
Sorry for the uncleared question.Here I will Call stored procedure 'B' from 'A' by passing one parameter.Based on the parameter value ,the Stored procedure B will retrun data multiple rows to A.
Post #872652
Posted Thursday, February 25, 2010 6:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 12,755, Visits: 31,122
amalanto in that case you have to create a table which will capture the results inside the stored procedure; so you need to know the structure and datatypes returned;something like this:
create procedure myproc 
as
begin

create table #tmp(tmpid int, columnlist.....)

insert into #tmp(tmpid,columnlist.....)
EXEC myotherProc

--do more stuff to the contents of #tmp
end



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #872653
Posted Thursday, February 25, 2010 7:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 22, 2013 5:34 AM
Points: 58, Visits: 151
Thank you sir.

Right now We have achieved by calling UDF.I want to know whether is it poassible through stored procedure.

Create Function dbo.Result(@TranId int)

Returns @t Table(@col1 varchar(10),@col2 Datetime)

Begin
Insert into @t select Status,Date from TransHistory where Transactionid=@TranId

return

end
Post #872708
Posted Thursday, February 25, 2010 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 12,755, Visits: 31,122
amalanto (2/25/2010)
Thank you sir.
Right now We have achieved by calling UDF.I want to know whether is it poassible through stored procedure.
Create Function dbo.Result(@TranId int)
Returns @t Table(@col1 varchar(10),@col2 Datetime)
Begin
Insert into @t select Status,Date from TransHistory where Transactionid=@TranId
return
end


not enough info; your function above returns a table of values based on a single Transactionid;
we need to know what the "outer" procedure is doing to help you; is it calling this function multiple times?
if it is, you should remove the function and do a JOIN in the "outer" procedure instead. you never want to use a function to get similar data multiple times.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #872720
Posted Saturday, February 27, 2010 8:11 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
amalanto (2/25/2010)
Hi,

I am using sql server 2005,In that we have one stored procedure A to call stored procedure B, which returns a set of records from a table. Then, I want stored procedure A to perform work on these records. How do I call stored procedure B from within stored procedure A?

Thanks in advance.



I think you can use this logic and try this works as the same way you asked B to A
in the below Eg only one output has been given in your case you can use your records in the OUTPUT.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.usp_GetList;
GO
CREATE PROCEDURE Production.usp_GetList @product varchar(40)
, @maxprice money
, @compareprice money OUTPUT
, @listprice money OUT
AS
SELECT p.name AS Product, p.ListPrice AS 'List Price'
FROM Production.Product p
JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.name LIKE @product AND p.ListPrice < @maxprice;
-- Populate the output variable @listprice.
SET @listprice = (SELECT MAX(p.ListPrice)
FROM Production.Product p
JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.name LIKE @product AND p.ListPrice < @maxprice);
-- Populate the output variable @compareprice.
SET @compareprice = @maxprice;
GO


DECLARE @compareprice money, @cost money
EXECUTE Production.usp_GetList '%Bikes%', 700,
@compareprice OUT,
@cost OUTPUT
IF @cost <= @compareprice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'



Thanks
Parthi
Post #874102
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse