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

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

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

Show all headers | View raw

On Monday, June 18, 2012 8:34:59 PM UTC+1, 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?
> 
> Thanks

You could take out the GOs and use "dynamic SQL".
This runs as a separate batch, but there are 
various ways to sneak a variable in.

DECLARE
      @seed  varchar(12)

SET @seed = 49

EXEC (
N'
CREATE TABLE example
    (
      i    int IDENTITY(' + @seed + N', 1)
    )
--X' )

I favour putting the SQL string in an
nvarchar(max) variable declaration, without 
quote breaks (which don't work beyond 4000/8000
characters in SQL Server 2005) but with tokens 
such as @{seed} in the string, to be substituted 
by doing REPLACE() as many times as necessary.
You can then test that you have still got a 
complete string terminating with '--X' - which 
is what that's for - and ideally not containing 
any '@{...' that you forgot or mistyped.
If the substituting text is an object reference,
it should be '[qualified].[and].[delimited]'
in the substituting value where appropriate.

If you want a quote mark inside the string, 
you have to type two quotes, which is a bit of 
a nuisance, but doesn't count as a break.

A statement of EXEC sp_executesql ...
allows you to treat a string as a stored 
procedure, with input and output parameters -
variables.  This can be combined with the token
method, which lets you use variables where 
T-SQL doesn't let you use variables.  A few 
commands with security implications may 
be excluded from being used in this way.

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