Call Another stored procedure which returns muliple rows

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Call Another stored procedure which returns...

Call Another stored procedure which returns muliple rows

Rate Topic

Display Mode

Topic Options

Author

Message

amalanto

Posted Thursday, February 25, 2010 5:48 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 10:16 AM
Points: 51, Visits: 138

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

bitbucket-25253

Posted Thursday, February 25, 2010 6:01 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 5,103, Visits: 20,220

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

amalanto

Posted Thursday, February 25, 2010 6:14 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 10:16 AM
Points: 51, Visits: 138

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

Lowell

Posted Thursday, February 25, 2010 6:19 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 11,648, Visits: 27,768

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

amalanto

Posted Thursday, February 25, 2010 7:13 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 10:16 AM
Points: 51, Visits: 138

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

Lowell

Posted Thursday, February 25, 2010 7:21 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 11,648, Visits: 27,768

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

parthi-1705

Posted Saturday, February 27, 2010 8:11 PM

Mr 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)

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 »

Permissions