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

Groups > comp.databases.ms-sqlserver > #1130 > unrolled thread

Newbie question: how to use vars in TSQL DDL

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1130 — Newbie question: how to use vars in TSQL DDL

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

[toc] | [next] | [standalone]

#1132

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.

[toc] | [prev] | [next] | [standalone]

#1133

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

[toc] | [prev] | [next] | [standalone]

#1135

Many thanks everybody.

Much appreciated.

"Gene Wirchenko" <genew@ocis.net> wrote in message 
news:l84vt7tk5g04vau789bc87c2sm1eb1h2b4@4ax.com...
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 

[toc] | [prev] | [next] | [standalone]

#1134

Mojo (please@dont.spam.com) writes:
> 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, ... ....
 
As long as you are only setting parameters to the script, you can use
SQLCMD. I am not here going to show an example, because I don't use
SQLCMD variables myself. Rather read the topic on SQLCMD in Books
Online.

Keep in mind that these variables are set client-side, so there can be 
no interaction what is going in SQL Server.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1136

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.

[toc] | [prev] | [next] | [standalone]

#1137

On Tuesday, June 19, 2012 11:50:11 AM UTC+1, rja.ca...@gmail.com wrote:
> 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, " ');"

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web