Groups > comp.databases.ms-sqlserver > #1132

Re: Newbie question: how to use vars in TSQL DDL

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	2012-06-18 16:52 -0400
Organization	A noiseless patient Spider
Message-ID	<jro4ea$o7g$1@dont-email.me> (permalink)
References	<ivGdnW3TprlqH0LSnZ2dnUVZ8kCdnZ2d@brightview.co.uk>

Cross-posted to 4 groups.

Show all headers | View raw

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.

Thread

Newbie question: how to use vars in TSQL DDL "Mojo" <please@dont.spam.com> - 2012-06-18 20:34 +0100
  Re: Newbie question: how to use vars in TSQL DDL "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-06-18 16:52 -0400
  Re: Newbie question: how to use vars in TSQL DDL Gene Wirchenko <genew@ocis.net> - 2012-06-18 13:55 -0700
    Re: Newbie question: how to use vars in TSQL DDL "Mojo" <please@dont.spam.com> - 2012-06-18 22:43 +0100
  Re: Newbie question: how to use vars in TSQL DDL Erland Sommarskog <esquel@sommarskog.se> - 2012-06-18 23:42 +0200
  Re: Newbie question: how to use vars in TSQL DDL rja.carnegie@gmail.com - 2012-06-19 03:50 -0700
    Re: Newbie question: how to use vars in TSQL DDL rja.carnegie@gmail.com - 2012-06-19 03:57 -0700

csiph-web