How to return the value from a query with SP_EXECUTESQL in a stored procedure?
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 the table, 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?
You could use a table variable to hold the outcomes 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 to get SQL server sp_executesql resolve.
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