Get result from dynamic SQL in stored procedure

Question

I am writing a stored procedure where I need to dynamically build an SQL statement in a procedure to reference the passed table name.

I need this SQL statement to return a result that I can then use throughout the procedure.

I tried using temporary tables and that’s it, but I keep getting the message that I need to declare a 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 a way to do this without resorting to using the actual table?

Thanks.

Answer 1

You tried 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 need to make sure that you prefix nvarchar strings with N, for example. SELECT @SQL = N'SELECT ...

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

Answer 2

First of all, you need to know that with dynamic SQL you risk an SQL injection attack, and you should first look at this link before using it. After that, you can change your first request to:

 DECLARE @FiscalYear INT DECLARE @DataSource NVARCHAR(25) DECLARE @SQL NVARCHAR(250) SELECT @DataSource = 'CustomerCosts20120328' DECLARE @tempFiscalYear TABLE ( FiscalYear INT ) SELECT @SQL = 'SELECT DISTINCT FiscalYear FROM ' + @DataSource INSERT INTO @tempFiscalYear EXEC(@SQL) SELECT @FiscalYear = FiscalYear FROM @tempFiscalYear 

You should also take into account @AaronBertrand's comment on the @FiscalYear parameter @FiscalYear .