7

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!

Community
  • 1
  • 1
Thomas Mondel
  • 1,944
  • 3
  • 20
  • 25

2 Answers2

8

"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?).

rocketmonkeys
  • 5,473
  • 5
  • 28
  • 21
  • Using a value across GO blocks is actually exactly what we use setvar for. – Josh Jun 18 '18 at 18:22
  • @Josh said: > Using a value across GO blocks is actually exactly what we use setvar for.: Yes, but are you setting those values to – David P Nov 06 '18 at 08:36
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?

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • I want to set some values and then run a script using these values. For example: Set id to 1, run the script which inserts some additional data for that entry with id 1. And later re-use it with id set to 2. So basically a simple function call but with script files. – Thomas Mondel Jul 11 '13 at 02:07
  • So do that. You can do something like `:setvar ID 1` and then in the SQL portion of the script do `insert into dbo.tbl ($(ID))` or assign it to a SQL variable (e.g. @id). For all intents and purposes, treat $(ID) like static text that's just replaced with the value of the variable by sqlcmd at run time. – Ben Thul Jul 11 '13 at 16:05