How to run a Stored Procedure based on the number of return values of another Stored Procedure

Posted by Mahendrabasutkar under Sql Server on 10/4/2011 | Points: 10 | Views : 1476 | Status : [Member] | Replies : 2
Hi All,

How to run a Stored Procedure based on the number of return values of another Stored Procedure, in SQL SERVER 2005

Scenario:
Stored Procedure 'ABC' returns a number of values, Say 3,2,5 i.e Three values
Stored Procedure 'xyz' has to run three times


Thanks&Regards,
Mahendra




Responses

Posted by: Ndebata on: 10/4/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Mahendra,

I am afraid directly you can not use data generated by the select statement inside a stored procedure.
Better way is to create a table valued function so that you can use those result directly in a query in your other stored procedure.

There is a workaround to use select result from SP.
Declare a table variable with the column type same as returning column in the same sequence.
Then insert into this local table and use this.
lets say in my sp there is a select statement selecting two columns ID and Name.
Declare @localtable Table(ID int,Name nvarchar(50));


Thanks,
Debata

Insert INTO @localtable
exec getallcountry
--Use this table as a normal table
Select * from @localtable







Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vishvvas on: 10/5/2011 [Member] [MVP] HonoraryPlatinum | Points: 25

Up
0
Down
If I undertood correctly, you need
1. To determine the no of return parameters of a stored procedure
2. Execute another procedure the times the no of parameters

One can have metadata of stored procedure to determine the retutn parameters
SET FMTONLY ON;
GO
EXEC dbo.sp_Test @parameter1 = NULL, @parameter2 = NULL
GO
SET FMTONLY OFF;
GO
This returns the information about the columns returned by stored procedure and once you have this metadata, it can be utilized for executing the another stored procedure.
Hope this helps.


Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response