SQLCMD variable scripting - injecting a variable into another

• d_sysuk

  SSC Eights!

  Points: 809

  More actions

  January 19, 2011 at 5:14 am

  #388669

  Okay this should be possible; if the injection is working correctly. What I am trying to do, is have a sqlcmd variable inject its value into another sqlcmd variable. Any ideas what I'm doing wrong ?

  (Hint copy into SSMS, and select Query -> SQLCMD mode to run this)

  :SETVAR DB_NAME_ASSIGN "TEST TEXT"

  :SETVAR DB_NAME_TEXT "EXPECT INSERT OF DB_NAME_ASSIGN HERE: $(DB_NAME_ASSIGN)"

  /*Get output*/

  SELECT '$(DB_NAME_TEXT)' AS OUTPUT

  SELECT '$(DB_NAME_ASSIGN)' AS OUTPUT

  /*Expect DB_NAME_TEXT OUTPUT to be:*/

  /* DB_NAME_TEXT "EXPECT INSERT OF DB_NAME_ASSIGN HERE: TEST TEXT*/

  /*Unfortunatly I get : EXPECT INSERT OF DB_NAME_ASSIGN HERE: $(DB_NAME_ASSIGN)*/

  Any ideas what I can do to get this to work (as I kind of need nested variable injection - nothing in BOL saying its not supported, possibly a bug)?

  [SQL 2008 SP1 Dev Edition]

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  January 24, 2011 at 11:55 am

  #1277403

  I spent a few minutes looking around and experimenting and I did not see a way to do it. I don't know everything you're trying to do, however while it may not be your preference this technique might work for you:

  :SETVAR DB_NAME_ASSIGN "TEST TEXT"

  :SETVAR DB_NAME_TEXT "EXPECT INSERT OF DB_NAME_ASSIGN HERE: "

  /*Get output*/

  SELECT '$(DB_NAME_TEXT)' + '$(DB_NAME_ASSIGN)' AS OUTPUT

  /* Delivers:

  EXPECT INSERT OF DB_NAME_ASSIGN HERE: TEST TEXT

  */

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• d_sysuk

  SSC Eights!

  Points: 809

  More actions

  January 25, 2011 at 3:25 am

  #1277699

  Yes, had to take the approach of breaking it down into further variables, then concatting the results together.

  Seems like it would be a nice thing to have the ability to inject sqlcmd var values into another one.