Setting SQL Variable via Dynamic SQL

Question

I know I am overthinking this, but I've been banging against this for too long so I'm reaching out for help.

This is the statement I'm trying to run: SELECT @cntMax = MAX(id) FROM [Raw_Item-FieldReport] BUT, the table name is a variable @reportTable

This doesn't work:

SET @sql = 'SELECT @cntMax = MAX(id) FROM @reportTable'
EXEC sp_executesql @sql

I even tried having the actual table name in the SET @sql and that doesn't work either. I didn't think it would be this difficult, please tell me I'm missing something easy/obvious.

Here's the full bit of code for those who want it:

DECLARE
    @inTable nvarchar(255) = 'Raw_Item',
    @reportTable nvarchar(255),
    @fieldName nvarchar(255),
    @cnt int,
    @cntMax int,
    @sql nvarchar(max)

SET @reportTable = @inTable + '-FieldReport'

SET @cnt = 1
SELECT @cntMax = MAX(id) FROM [Raw_Item-FieldReport]
PRINT @cntMax
SET @cntMax = 0

SET @sql = 'SELECT @cntMax = MAX(id) FROM [Raw_Item-FieldReport]'
EXEC sp_executesql @sql
PRINT @cntMax

SQL Server 12.0.2008.8 (on Azure)

The dynamic table name is problematic for sure. You can't parameterize object names. You would have to create a string with the correct table name. This is very often a sign of some design issues. — Sean Lange
– Sean Lange, Commented Oct 13, 2021 at 19:18

Aaron Bertrand · Accepted Answer · 2021-12-28 03:10:06Z

3

You need to use an output parameter, otherwise SQL Server has no idea how to connect @cntMax in the dynamic SQL to @cntMax not in the dynamic SQL, since they are different scopes. And to protect yourself from SQL injection (some tips here and here), always check that your object exists, and use QUOTENAME() as opposed to manually adding square brackets (and you should always use QUOTENAME() when building object names from user input or variables, even when they don't have bad characters like dashes):

DECLARE @sql         nvarchar(max),
        @inTable     nvarchar(255) = N'Raw_Item',
        @reportTable nvarchar(255);

SET @reportTable = N'dbo.' + QUOTENAME(@inTable + '-FieldReport');

IF OBJECT_ID(@reportTable) IS NOT NULL
BEGIN
  SET @sql = N'SELECT @cntMax = MAX(id) FROM ' + @reportTable + N';';

  EXEC sys.sp_executesql @sql,
           N'@cntMax int output', 
           @cntMax = @cntMax OUTPUT;

  PRINT @cntMax;
END
ELSE
BEGIN
  PRINT 'Nice try, h@xx0rs!';
END

Always use schema reference (dbo), always use statement terminators, and please try to avoid naming things with invalid identifier characters like dash (-). And one additional tip: always use N prefix on N'nvarchar string literals'.

edited Dec 28, 2021 at 3:10

answered Oct 13, 2021 at 19:10

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dizzy49 Over a year ago

Thanks this worked great. And thanks for the tips, I've incorporated them into the project :D

Collectives™ on Stack Overflow

Setting SQL Variable via Dynamic SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related