Path: csiph.com!usenet.pasdenom.info!news.albasani.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From: "Bob Barrows" <reb01501@NOyahooSPAM.com>
Newsgroups: comp.databases.ms-sqlserver,microsoft.public.sqlserver,microsoft.public.sqlserver.misc,microsoft.public.sqlserver.programming
Subject: Re: Newbie question: how to use vars in TSQL DDL
Date: Mon, 18 Jun 2012 16:52:19 -0400
Organization: A noiseless patient Spider
Lines: 35
Message-ID: <jro4ea$o7g$1@dont-email.me>
References: <ivGdnW3TprlqH0LSnZ2dnUVZ8kCdnZ2d@brightview.co.uk>
Injection-Date: Mon, 18 Jun 2012 20:52:27 +0000 (UTC)
Injection-Info: mx04.eternal-september.org; posting-host="NUmboHSNYG4cX5Ys9j8TNw"; logging-data="24816"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18geFRA8qU5/xTH1MKRCTrj6ArIAhExaA0="
X-Newsreader: Microsoft Outlook Express 6.00.2800.2001
Cancel-Lock: sha1:TIX3HpkHvly4VM2LmqIegUGQgTg=
X-Priority: 3
X-MSMail-Priority: Normal
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.2001
Xref: csiph.com comp.databases.ms-sqlserver:1132

Mojo wrote:
> 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?
>
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.