1

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!

TangoAlee
  • 1,260
  • 2
  • 13
  • 34
  • 1
    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 Oct 30 '15 at 19:07

2 Answers2

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.

devlin carnate
  • 8,309
  • 7
  • 48
  • 82
2
  1. Use correct types SYSNAME for idetifiers, NVARCHAR(MAX) for query
  2. Use REPLACE instead of concatenating it will be much easier to debug especially when you need string literals
  3. 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] )
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Additional Question - what does the `N'` stand for at the beginning of `dynSQL`? – TangoAlee Nov 02 '15 at 14:43
  • 1
    [nvarchar, versus varchar](http://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar) – devlin carnate Nov 02 '15 at 15:29
  • 1
    @TangoAlee Link above give good description. NVARCHAR() and N'' before string means support for unicode characters. – Lukasz Szozda Nov 02 '15 at 15:54
  • 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: http://stackoverflow.com/questions/33486521/dynamic-ms-sql-variable-parsing-incorrectly-from-cursor – TangoAlee Nov 02 '15 at 20:44
  • 1
    @TangoAlee Just use `QUOTENAME` function (see point 3), but you've got this answer already in your new question – Lukasz Szozda Nov 03 '15 at 09:50