Return value from exec(@sql)

sql

prompt-engineering

best-practices

data-usage

byAlex Kataev·Dec 23, 2024

Harness output parameters with sp_executesql to capture dynamic SQL's returned data. Declare a variable, include it in your query and designate it as OUTPUT:

DECLARE @Result NVARCHAR(100);
EXEC sp_executesql N'SELECT @Result = MyColumn FROM MyTable WHERE ...',
                   N'@Result NVARCHAR(100) OUTPUT', 
                   @Result OUTPUT;
SELECT @Result;

Capturing complex data using TABLE variables

When you predict multiple rows or columns in your returned data, crafting a table variable can be quite the tool. Here's how to declare and fill a table variable with dynamic SQL results:

DECLARE @ResultsTable TABLE (Column1 INT, Column2 NVARCHAR(100));
-- This is SQL, not a shopping cart, but you're still putting items into a basket, right?
INSERT INTO @ResultsTable
EXEC sp_executesql N'SELECT Column1, Column2 FROM MyTable WHERE ...';
SELECT * FROM @ResultsTable;

Quick capture with SELECT INTO

Fetching a single row doesn't need to be a hassle. With SELECT INTO, assign values directly within dynamic SQL:

DECLARE @Result NVARCHAR(100);
-- CONCAT MAGIC HAPENNING HERE
EXEC(@sql + 'SELECT @Result = Column1 INTO #TempTable FROM MyTable WHERE ...');
-- Catch me if you can!
SELECT @Result = Column1 FROM #TempTable;
-- Bye bye, table. You will be remembered.
DROP TABLE #TempTable;

Execution clean-up with SET NOCOUNT ON

You don't want sneaky side-messages like 'rows affected' meddling with the fruits of your hard work:

-- Bang and the noise is gone!
SET NOCOUNT ON;
-- Your dynamic SQL execution
SET NOCOUNT OFF;

What's your status? Checking with @@ROWCOUNT and @@ERROR

For your own peace of mind, always check @@ROWCOUNT and @@ERROR:

DECLARE @RowCount INT, @Error INT;
-- After executing dynamic SQL
SET @RowCount = @@ROWCOUNT; -- Feeling productive!
SET @Error = @@ERROR; -- Any nasty bugs around?

Catering for various data

Make sure you're prepared for different types of alien life:

DECLARE @IntResult INT, @DateResult DATETIME;
-- Make sure these guys are compatible with the data types you expect

Sweeping up after yourself

You don’t want tomorrow’s mission to stumble over today’s clutter. Mop up those temporary tables after use:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
    DROP TABLE #TempTable;  -- Sweep sweep, gone in a blink!

Using status beacons

Send clear signals back to base with explicit return codes in your stored procedures:

IF @Error != 0
    BEGIN
        RETURN @Error;  -- Houston, we have a problem.
    END
ELSE
    BEGIN
        RETURN 0;  -- Mission accomplished, crew!
    END

Post-mission data usage

You don't have to stick to just looking at pretty space photos. Get hands-on with the data stored in table variables or temp tables for further exploration:

-- Using data from the @ResultsTable probe
UPDATE AnotherTable
SET AnotherTable.Column1 = r.Column1
FROM @ResultsTable r
WHERE AnotherTable.ID = r.ColumnID;  -- Setting new frontiers.