XPost: microsoft.public.sqlserver, microsoft.public.sqlserver.misc,   
   microsoft.public.sqlserver.programming   
   From: genew@ocis.net   
      
   On Mon, 18 Jun 2012 20:34:59 +0100, "Mojo"    
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)