How to use :setvar properly?

Question

I call several sql files using:

:r C:\Scripts\Script1.sql
:r C:\Scripts\Script2.sql
:r C:\Scripts\Script3.sql

I have this idea about the :r call from here: TransactSQL to run another TransactSQL script. The Script1.sql script might as well also have other sql script calls in its code, etc.

But now I want to define settings for each script. For example: I define LastInsertedID and set it to the value of SCOPE_IDENTITY() right before the the call for Script1.sql. And this script now uses this variable and works with it.

To do so I used sqlcmd script variables (http://msdn.microsoft.com/de-de/library/ms188714.aspx) and was setting them by using:

:setvar LastInsertedID SCOPE_IDENTITY()

I could then write SELECT $(LastInsertedID in the Script1.sql and it would give me the correct value.

Later I found out that this is not correct at all because if you process other INSERTstatements before you select $(LastInsertedID) it would give you the ID of the newly inserted row. This might be the case because :setvar does not save the value of that current SCOPE_IDENTITY() but furthermore a reference to it and calls it again once it is requested.

So I tried something different and declared a variable, assigned it the current value of SCOPE_IDENTITY() and then saved it with :setvar. It looked something like this:

DECLARE @LastInsertedID int
SELECT @LastInsertedID = SCOPE_IDENTITY()
:setvar LastInsertedID @LastInsertedID
PRINT $(LastInsertedID)

This again worked for the moment and delivered the correct result. But then I separated my code into multiple GO sections and realized that the :setvar again delivers not the desired output.

When you insert a GO after the :setvar in the example before:

DECLARE @LastInsertedID int
SELECT @LastInsertedID = SCOPE_IDENTITY()
:setvar LastInsertedID @LastInsertedID

GO

PRINT $(LastInsertedID)

It now gives you a error message saying: Must declare the scalar variable @LastInsertedID.

Again :setvar does not save the actual value of the variable @LastInsertedID but furthermore a reference to the variable itself, which of course does not exist in that given context anymore.

My question now is, how do I use :setvar properly or how can I solve this issue differently with the desired output?

I appreciate any help or input!

Answer 1

"Again :setvar does not save the actual value of the variable @LastInsertedID but furthermore a reference to the variable itself, which of course does not exist in that given context anymore."

Setvar doesn't really save a reference to the variable. Setvar (and variable substitution) are preprocessors, so it's the same thing as running a text-editor search & replace on your file just before running it.

This may make things more clear. SETVAR & substitution happens before anything else. So this:

DECLARE @LastInsertedID int
SELECT @LastInsertedID = SCOPE_IDENTITY()
:setvar LastInsertedID @LastInsertedID
GO
PRINT $(LastInsertedID)

Turns into this:

DECLARE @LastInsertedID int
SELECT @LastInsertedID = SCOPE_IDENTITY()
GO
PRINT @LastInsertedID

Then it's run by SQL server. This does not work, since @LastInsertedID is no longer defined after the GO statement.

Setvar can't be used to preserve a value across GO blocks. Each block has it's own scope & own set of SQL variables. You need to run all that code in the same GO block, or find another way to pass data between two different blocks (temp tables? global temp tables? proc calls?).

Answer 2

The sqlcmd directives (e.g. :setvar) don't communicate with your server at all. Think of them as a primitive macro. They're more for changing things that you'll know at run time. I suppose that you could come up with some fancy way to have sqlcmd output a file of :setvar statements and then read it in with another invocation to sqlcmd, but that seems overly cumbersome and fragile. What are you trying to accomplish?