Dynamic MS SQL Variable Parsing incorrectly

Question

I have got an issue with Dynamic Variable Parsing incorrectly and I don't know a way around it.

Here is an example:

DECLARE @tableName varchar(20),
    @columnName varchar(20),
    @prompt varchar(20)

DECLARE @dynSQL varchar(500)


set @tableName = '[A1-ExciseESN]'
set @columnName = '[Anode Excise ESN (A1)]'

@dynSQL = 'INSERT INTO ' + @tableName +'
([trav num], '+@columnName+')
Select [trav num], '+@columnName+'
FROM [temprmi$] t1
PIVOT(min([Lvl1 Trace Data])
FOR [Prompt Text] IN ('+@columnName+'
   )
   ) AS PVTTable
     where '+@columnName+' is not null and [trav num] not in (select [trav num] from '+@tableName+')'


print @dynSQL

Here is the output of the print @dynSQL

INSERT INTO [A1-ExciseESN]
([trav num], [Anode Excise ESN (A) 
Select [trav num], [Anode Excise ESN (A
FROM [temprmi$] t1
PIVOT(min([Lvl1 Trace Data])
FOR [Prompt Text] IN ([Anode Excise ESN (A
   )
   ) AS PVTTable
     where [Anode Excise ESN (A is not null and [trav num] not in (select [trav num] from [A1-ExciseESN])

I've tried several methods but can't seem to make it work. Thanks in advance for the help!

In Dynamic SQL, you can save yourself some syntax headache and readability by just using your variable name in the string and then encapsulating the whole string with a REPLACE() to substitute out your variable with its value at the end. For example: 'INSERT INTO ' + @tableName +' String two' becomes REPLACE('INSERT INTO @tableName, String two','@tableName',@tableName) — EMUEVIL
– EMUEVIL, Commented Oct 30, 2015 at 19:07

Lukasz Szozda · Accepted Answer · 2015-10-30 19:08:38Z

2

Use correct types SYSNAME for idetifiers, NVARCHAR(MAX) for query
Use REPLACE instead of concatenating it will be much easier to debug especially when you need string literals
Consider using QUOTENAME function

Code:

DECLARE 
    @tableName  SYSNAME,
    @columnName SYSNAME,
    @prompt     VARCHAR(20),
    @dynSQL     NVARCHAR(MAX);

SET @tableName  = QUOTENAME('A1-ExciseESN');
SET @columnName = QUOTENAME('Anode Excise ESN (A1)');

SET @dynSQL = 
N'INSERT INTO  <tableName> 
 ([trav num], <columnName>)
Select [trav num], <columnName>
FROM [temprmi$] t1
PIVOT(min([Lvl1 Trace Data])
FOR [Prompt Text] IN (<columnName>)
   ) AS PVTTable
     where <columnName> is not null 
        and [trav num] not in (select [trav num] from <tableName> )'

SET @dynSQL = REPLACE(REPLACE(@dynSQL, '<tableName>', @tableName),
               '<columnName>', @columnName);

PRINT @dynSQL;

LiveDemo

Output:

INSERT INTO  [A1-ExciseESN] 
 ([trav num], [Anode Excise ESN (A1)])
Select [trav num], [Anode Excise ESN (A1)]
FROM [temprmi$] t1
PIVOT(min([Lvl1 Trace Data])
FOR [Prompt Text] IN ([Anode Excise ESN (A1)])
   ) AS PVTTable
     where [Anode Excise ESN (A1)] is not null 
       and [trav num] not in (select [trav num] from [A1-ExciseESN] )

answered Oct 30, 2015 at 19:08

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

5 Comments

TangoAlee Over a year ago

Additional Question - what does the N' stand for at the beginning of dynSQL?

devlin carnate Over a year ago

nvarchar, versus varchar

Lukasz Szozda Over a year ago

@TangoAlee Link above give good description. NVARCHAR() and N'' before string means support for unicode characters.

TangoAlee Over a year ago

I posted another question - This worked great now I'm supplying the @columnname and @tablename from a cursor and it isn't supplying the [] any more. see: stackoverflow.com/questions/33486521/…

Lukasz Szozda Over a year ago

@TangoAlee Just use QUOTENAME function (see point 3), but you've got this answer already in your new question

devlin carnate · Accepted Answer · 2015-10-30 19:01:56Z

2

You've defined:

@columnName varchar(20),

But you're assigning:

set @columnName = '[Anode Excise ESN (A1)]'

columnname needs to be at least varchar(23). the column name you're using is 23 characters long.

answered Oct 30, 2015 at 19:01

devlin carnate

8,6617 gold badges55 silver badges106 bronze badges

1 Comment

TangoAlee Over a year ago

Thank you - I would have never checked that!

Collectives™ on Stack Overflow

Dynamic MS SQL Variable Parsing incorrectly

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related