Newbie question: how to use vars in TSQL DDL

[Mojo]

Hi All

Apols if this a noddy question, but I just can't fathom it!!! :0)

I use a long SQL script (DDL ??) to drop, create and populate my db each
time (rather than a backup) and initially the DB needs key values inserted
into it.

At the mo, I try to remember to scroll up and down the script (quite long
now) to populate it with the required values for the given time, but I much
rather do what I used to do in MySQL, which was to put varaibles at the very
top of my script so that the values entered at the top then reflect further
down, eg

Line 1 : SET @MyYear= 2012;
...
...
...
Line 304: .INSERT ... .... ....., @MyYear, ... ....

I've tried this, but it appears as though my GO statements stop it from
working. I'm probably wrong, but this seems to suggest that I need to set
the var about 1 or 2 rows above the actual INSERT, which defeats my purpose.

Is there a way round this?

Thanks

[Bob Barrows]

Post by Mojo
Hi All
Apols if this a noddy question, but I just can't fathom it!!! :0)
I use a long SQL script (DDL ??) to drop, create and populate my db
each time (rather than a backup) and initially the DB needs key
values inserted into it.
At the mo, I try to remember to scroll up and down the script (quite
long now) to populate it with the required values for the given time,
but I much rather do what I used to do in MySQL, which was to put
varaibles at the very top of my script so that the values entered at
the top then reflect further down, eg
...
...
...
I've tried this, but it appears as though my GO statements stop it
from working. I'm probably wrong, but this seems to suggest that I
need to set the var about 1 or 2 rows above the actual INSERT, which
defeats my purpose.
Is there a way round this?

The only way is via CONTEXT_INFO(). See
http://weblogs.sqlteam.com/robv/archive/2012/06/05/t-sql-tuesday-31-logging-tricks-with-context_info.aspx

But be aware of its limitations, especially being limited to
varbinary(128) - in your case, that doesn't seem to be an obstacle.

[Gene Wirchenko]

Post by Mojo
Apols if this a noddy question, but I just can't fathom it!!! :0)
I use a long SQL script (DDL ??) to drop, create and populate my db each
time (rather than a backup) and initially the DB needs key values inserted
into it.

I was asking about this recently, too, in
comp.databases.ms-sqlserver and microsoft.public.sqlserver.programming
(two of the four newsgroups you posted to). I wanted to do about the
same as you.

Post by Mojo
At the mo, I try to remember to scroll up and down the script (quite long
now) to populate it with the required values for the given time, but I much
rather do what I used to do in MySQL, which was to put varaibles at the very
top of my script so that the values entered at the top then reflect further
down, eg
...
...
...
I've tried this, but it appears as though my GO statements stop it from
working. I'm probably wrong, but this seems to suggest that I need to set
the var about 1 or 2 rows above the actual INSERT, which defeats my purpose.

A GO statement terminates a batch. A variable's lifetime is that
of the batch that it is declared in.

Post by Mojo
Is there a way round this?

I was defining stored procedures which have to be delimited by GO
statements (or BOF/EOF) and following each with its test. Any
variables set at the beginning would be blown away by the first GO,
and I had plenty of them.

You could populate a temp table at the start of your script and
interrogate it later as needed. Temp tables have a lifetime of the
session (if not dropped sooner).

Population:
create table #UnkillableVariables
(
RoughAndToughVariable int
...
);
insert into #UnkillableVariables
(
RoughAndToughVariable int
...
)
values
(
2012
...
);

Interrogation:
declare @RoughAndToughVariable int;
select @RoughAndToughVariable=RoughAndToughVariable
from #UnkillableVariables;
then use it in that batch.

The definition part is lengthy, but the use part is not. I did
not bother, but I might if I revisit this and the script has a long
lifetime.

Sincerely,

Gene Wrichenko

[Mojo]

Many thanks everybody.

Much appreciated.

Post by Mojo
Apols if this a noddy question, but I just can't fathom it!!! :0)
I use a long SQL script (DDL ??) to drop, create and populate my db each
time (rather than a backup) and initially the DB needs key values inserted
into it.

I was asking about this recently, too, in
comp.databases.ms-sqlserver and microsoft.public.sqlserver.programming
(two of the four newsgroups you posted to). I wanted to do about the
same as you.

Post by Mojo
At the mo, I try to remember to scroll up and down the script (quite long
now) to populate it with the required values for the given time, but I much
rather do what I used to do in MySQL, which was to put varaibles at the very
top of my script so that the values entered at the top then reflect further
down, eg
...
...
...
I've tried this, but it appears as though my GO statements stop it from
working. I'm probably wrong, but this seems to suggest that I need to set
the var about 1 or 2 rows above the actual INSERT, which defeats my purpose.

A GO statement terminates a batch. A variable's lifetime is that
of the batch that it is declared in.

Post by Mojo
Is there a way round this?

I was defining stored procedures which have to be delimited by GO
statements (or BOF/EOF) and following each with its test. Any
variables set at the beginning would be blown away by the first GO,
and I had plenty of them.

You could populate a temp table at the start of your script and
interrogate it later as needed. Temp tables have a lifetime of the
session (if not dropped sooner).

Population:
create table #UnkillableVariables
(
RoughAndToughVariable int
...
);
insert into #UnkillableVariables
(
RoughAndToughVariable int
...
)
values
(
2012
...
);

Interrogation:
declare @RoughAndToughVariable int;
select @RoughAndToughVariable=RoughAndToughVariable
from #UnkillableVariables;
then use it in that batch.

The definition part is lengthy, but the use part is not. I did
not bother, but I might if I revisit this and the script has a long
lifetime.

Sincerely,

Gene Wrichenko

[Erland Sommarskog]

Post by Mojo
I use a long SQL script (DDL ??) to drop, create and populate my db each
time (rather than a backup) and initially the DB needs key values inserted
into it.
At the mo, I try to remember to scroll up and down the script (quite
long now) to populate it with the required values for the given time,
but I much rather do what I used to do in MySQL, which was to put
varaibles at the very top of my script so that the values entered at the
top then reflect further down, eg
....
....
....

As long as you are only setting parameters to the script, you can use
SQLCMD. I am not here going to show an example, because I don't use
SQLCMD variables myself. Rather read the topic on SQLCMD in Books
Online.

Keep in mind that these variables are set client-side, so there can be
no interaction what is going in SQL Server.