Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

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

Cross-posted to 4 groups.

Show all headers | View raw

On Mon, 18 Jun 2012 20:34:59 +0100, "Mojo" <please@dont.spam.com>
wrote:

>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.

>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.

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

>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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

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