How to return the value from a query with SP_EXECUTESQL in a stored procedure

Question

I have a stored procedure that generated a query string and then executes it with

EXECUTE SP_EXECUTESQL @Query

The query, e.g., is: Select Name, Age from tableA, so very simple.

But how to get the result set back from the SP_EXECUTESQL (into a variable?) and then return it as result set in my stored procedure?

• Denis Rubashkin: into a variable what do you mean? Your query result is a rowset.

Answer 1

You could use a table variable to hold the results of the dynamic SQL call until you are ready to exit the stored procedure. Just before returning from the stored procedure, select the data from the table variable.

DROP PROCEDURE IF EXISTS dbo.MyProc;
GO
CREATE PROCEDURE dbo.MyProc
AS
BEGIN
 --Declare Table variable to hold results of query
 DECLARE @Results TABLE (ResultText VARCHAR(500));
 DECLARE @Query NVARCHAR(30);
 --This is the query
 SET @Query = 'select @@version'
 --Insert the results of the dynamic SQL execution into the table variable
 INSERT INTO @Results
 EXECUTE SP_EXECUTESQL @Query
 --Do more stored procedure logic
 --Finally, select the results of the table variable
 SELECT *
 FROM @Results
END
go
EXEC dbo.MyProc

Answer 2

If you are returning singular results (e.g you want to store returned results in an @parameter int, rather than an @parameter table) then sp_executesql can use OUTPUT parameters

see

https://support.microsoft.com/en-gb/help/262499/how-to-specify-output-parameters-when-you-use-the-sp-executesql-stored

for worked examples of how to do this

Answer 3

Returning the resultset does not have to be the last thing that happens in a stored procedure. If you don't want to do things with the resultset in your procedure you don't have to store it in a temp table/table variable.

create or alter procedure dbo.GetVersionServer as
begin
 -- Execute your dynamic SQL here returning a rowset
 declare @S nvarchar(max) = N'select @@version as version union all select @@version';
 exec sp_executesql @S;
 -- Do some more stuff here
 declare @I int;
 set @I = @@rowcount;
 print 'Number of rows: '+cast(@I as varchar(11));
 -- You could even return a second rowset if you like
 set @S = N'select @@servername as servername';
 exec sp_executesql @S;
end;