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

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

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