Get result from dynamic SQL in stored procedure

sql, sql-server-2008, stored-procedures, t-sql

Solution

Did you try something like:

DECLARE @FiscalYear INT, @DataSource NVARCHAR(25), @SQL NVARCHAR(250);
SET @DataSource = N'CustomerCosts20120328';
SET @SQL = N'SELECT DISTINCT @FiscalYear = FiscalYear FROM ' + @DataSource;
EXEC sp_executesql @SQL, N'@FiscalYear INT OUTPUT', @FiscalYear OUTPUT;

PRINT @FiscalYear;

You'll want to make sure you prefix nvarchar strings with N, e.g. `SELECT @SQL = N'SELECT ...`.

Also, you know that if the query returns multiple rows, the value that gets assigned to `@FiscalYear` is completely arbitrary, right? While you may expect a single value from that table, it can't hurt to use `MAX()` or `TOP 1 ... ORDER BY` to ensure that only a single, predictable value is ever assigned.

Problem

I'm writing a stored procedure where I need to dynamically construct a SQL statement within the procedure to reference a passed in table name. I need to have this SQL statement return a result that I can then use throughout the rest of the procedure. I've tried using temp tables and everything but I keep getting a message that I need to declare the variable, etc. For example: ``` DECLARE @FiscalYear INT DECLARE @DataSource NVARCHAR(25) DECLARE @SQL NVARCHAR(250) SELECT @DataSource = 'CustomerCosts20120328' DECLARE @tempFiscalYear TABLE ( FiscalYear INT ) SELECT @SQL = 'INSERT INTO @tempFiscalYear SELECT DISTINCT FiscalYear FROM ' + @DataSource EXEC(@SQL) SELECT @FiscalYear = FiscalYear FROM @tempFiscalYear ``` Or... ``` DECLARE @FiscalYear INT DECLARE @DataSource NVARCHAR(25) DECLARE @SQL NVARCHAR(250) SELECT @DataSource = 'CustomerCosts20120328' SELECT @SQL = 'SELECT DISTINCT @FiscalYear = FiscalYear FROM ' + @DataSource EXEC(@SQL) ``` Is there anyway to do this without resorting to using an actual table? Thanks.

Original source