Get result from dynamic SQL in stored procedure

Question

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.

Answer 1

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.