Sql-server – How to return the value from a query with SP_EXECUTESQL in a stored procedure

sql serverstored-procedurest-sql

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?

Best Answer

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

Related Solutions

Mysql – How does MySQL return a result set from a stored procedure

Each SELECT statement that does not insert into a table or a variable will produce a result set.

If you want your stored procedure to return only one result set, make sure that you only have one SELECT statement. If you have other SELECT statements, make sure that they insert results into a table or variable.

UPDATE
Here are examples of stored procedures.

This stored procedure would return one result set:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;
END;;
DELIMITER ;

This stored procedure would return two result sets:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;

    SELECT * FROM table_2;
END;;
DELIMITER ;

Sql-server – Calling stored procedure with select and update statement

You can do this in a single statement using the OUTPUT clause:

UPDATE dbo.Table
SET Retrieved = 'Y'
OUTPUT inserted.x, inserted.y, inserted.z;

If you don't really mean to update the entire table every time:

UPDATE dbo.Table
SET Retrieved = 'Y'
OUTPUT inserted.x, inserted.y, inserted.z
WHERE <...where clause...>;

Best Answer

Related Solutions

Mysql – How does MySQL return a result set from a stored procedure

Sql-server – Calling stored procedure with select and update statement

Related Question