Dynamic Query w/ Quotes issue

Question

I'm trying to write a dynamic query that produces the same results as the following, but replaces the fixed tablename with a variable.

    SELECT *
    WHERE tableName = 'Table2A'

works fine but

    DECLARE @tablename AS NVARCHAR(100)
    SET @tablename = N'Table2A'
    DECLARE @execquery AS NVARCHAR(MAX)
    SET @execquery = N'
       SELECT *
       WHERE tableName = ''' + QUOTENAME(@tablename) + N''''    

    EXECUTE sp_executesql @execquery

returns no records. What am I doing wrong?

score 0 · Accepted Answer · answered Nov 02 '12 at 03:32

Aside from the fact that your SQL statement is invalid, QUOTENAME() simply places brackets "[]" around the supplied variable. Replace the EXECUTE statement with a PRINT statement, and you'll get the following results:

DECLARE @tablename AS NVARCHAR(100)
    SET @tablename = N'Table2A'
    DECLARE @execquery AS NVARCHAR(MAX)
    SET @execquery = N'
       SELECT *
       WHERE tableName = ''' + QUOTENAME(@tablename) + N''''    
PRINT @execquery
    --EXECUTE sp_executesql @execquery

RESULTS:

   SELECT *
   WHERE tableName = '[Table2A]'

Thanks! Good tip on the PRINT technique, I'll use that a lot. — Steve Wash, Nov 02 '12 at 08:51

Dynamic Query w/ Quotes issue

1 Answers1