Set sqlcmd :SetVar scripting variable to a T-SQL variable

• MrAkki

  SSCrazy

  Points: 2574

  More actions

  May 20, 2011 at 3:24 am

  #239978

  Hi,

  Is there any way to set a sqlcmd :SetVar scripting variable to a T-SQL variable.

  I have something like that:

  :Setvar F1 Test1

  ... some code ...

  DECLARE @SQL1 NVARCHAR(MAX) = 'Test2'

  :Setvar F1 @SQL1

  :OUT $(F1)

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  May 20, 2011 at 9:13 am

  #1327751

  It's not possible because sqlcmd vars and t-sql vars are evaluated independently in different and disparate steps of the execution of a sqlcmd script. This example illustrates:

  -- run in SSMS SqlCmd Mode

  :Setvar F1 Message1

  SELECT '$(F1)' AS [why doesn't it say Message1?']

  :Setvar F1 Message2

  SELECT '$(F1)' AS [because sqlcmd vars are eval'ed first...then T-SQL is run]

  What you can do is code a PowerShell script to manage all your dynamic variables and then pass them into sqlcmd.exe using -v to get a dynamic effect using saved sqlcmd sql scripts.

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