Post reply

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

  • 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)

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply