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.

Solution 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.

Solution 2

First of all, you need to know that with dynamic SQL you are risking an SQL injection attack, and you should first take a look at this link before using it. After you do that, you can change your first query 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 comment on the assignment of the @FiscalYear parameter.

Comments

[Tom]

Thanks for the tip. While that does return what I'm looking for, I don't need that value as the end result of the stored procedure. I need to store that value in a variable that I can then use througout the rest of the procedure.

[Adir D]

That's exactly what it does - it puts the value obtained within dynamic SQL into a variable in the outer scope. Add PRINT @FiscalYear; as the next line to confirm. Try code instead of questioning it. :-)

[Adir D]

Presumably the set of tables being appended to the query come from an internal list and not from a user on a web page somewhere. I'm all for warning about dynamic SQL, but not all uses of dynamic SQL deserve a warning bell. IMHO. Anyway I find the use of a table variable here to be overkill and unnecessary.

[Tom]

Sorry, I did try it. Had a rouge SELECT in there that threw me off for a minute. It works.

[Tom]

Thanks, I don't have to worry about SQL injection with this particular stored procedure but will keep that article handy for future reference.

[Lamak]

@AaronBertrand - Yeah, maybe I'm just being overzealous about it...its the force of habit.