CODEWITHSUNDEEP
sql-server
Lav
Lav

Reputation: 99

SQL Server stored procedure called from another stored proc returns command completed succesfully but no result set

I have two stored procedures; I am calling one stored proc from another.

I return a table from the 1st stored procedure. When I execute the 1st alone, I get the table correctly.

But when I call the 1st stored procedure from another stored procedure, it always returns command completed successfully and no results.

I call it like this in stored proc 2:

set @query = 'exec servername.dbo.storedproc1 @ClassName = ''' + 
             @ClassName +''', @StatusName = ''' + @StatusName 
exec(@query)

Upvotes: 0

Views: 2124

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

First create a temporary table that captures data obtained from executing first procedure . Then use Select statement to retrieve desired data from temporary table. Don't forget to use drop the temporary table table after Select statement else next time you execute the procedure error will be thrown saying table already exist . On other hand you can use table variable to avoid dropping table as scope of table variable is limited to lifetime of the containing stored procedure. `

Insert into @temptable
Exec sprcdre1

Select * from  @temptable

Upvotes: 0

Matan Yungman
Matan Yungman

Reputation: 111

Inside the outer procedure, create a temporary table with a similar schema to the result set returned from the inner procedure. When calling the inner procedure, use insert..exec, like this:

insert #tempTable exec InnerProcedure

Then select the data from the temporary table.

Upvotes: 1

Related Questions