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

#1

On Monday, June 18, 2012 8:34:59 PM UTC+1, Mojo wrote:

Quote:

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.

#2

On Tuesday, June 19, 2012 11:50:11 AM UTC+1, rja.ca... (AT) gmail (DOT) com wrote:

Quote:

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.

Upon reflecting a moment more, you could
replace every one ' with two, '' and then
replace every GO with,

'); EXEC (N'

plus add at the start of the script
"EXEC (N' " and at the end, " ');"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Newbie question: how to use vars in TSQL DDL - 06-19-2012 , 05:50 AM

Re: Newbie question: how to use vars in TSQL DDL - 06-19-2012 , 05:57 AM