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

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

Re: How to Get Local Variables That Are Not So Local

Show all headers | View raw

On Wednesday, May 30, 2012 3:55:28 AM UTC+1, Gene Wirchenko wrote:
>   1) I wish I could define a variable at the start of the script and
> check that with each of my tests.  Unfortunately, the scope of a local
> variable is but the batch it is defined in.  Or is there a way of
> defining a longer-lived variable?
> 
>   2) An alternative would be to create a table and insert a row to
> indicate whether to test.  That would be a kludge.
> 
>      T-SQL does seem to be missing some of the oddest things.  Is this
> another case of that, or is it that I just do not know about nonlocal
> variable declarations?

I agree with other advice that this isn't 
what Transact-SQL is /for/ and there isn't a 
much better way in T-SQL to do what you want
(so your solution is quite good), but there 
are some alternatives in T-SQL.

There is in fact a string of 128 bytes
associated with your SQL Server session, 
that you can set and read with some
rather arcane commands - but I've forgotten 
what they are.  That is quite clumsy too,
and I think that's why I'm not using it.
At least, not in SQL Server 2000, but they
may have made it nicer...  oh, here it is:
SET CONTEXT_INFO @newValue, returned as
CONTEXT_INFO(), and maybe what has annoyed 
me is that it's varbinary(128).

Otherwise:
If you use a temporary table then you don't
have to remember to delete it when you 
finish.  A global temporary table can be 
seen in any connection, and is dropped when 
everyone who has used it logs off, but for
that reason it isn't secure, as far as I 
can see.  That may not be a concern.

You could create a temporary procedure 
to do the tests, and create it in two 
versions, one that does the tests and one
that doesn't.  Then in the rest of the
script, call the procedure by name.

Or you could create a cursor, although 
that may be not a particularly good way
to do it.

You could "SET" and then test another
session property that you aren't 
particularly interested in, but these 
tend to be acquired permanently by 
objects that you create.  If you want 
to make it hard for /other/ people
to debug your programs, use 
SET ANSI_NULLS as the test switch...
but no, that will make it pretty difficult
for you, too.

Transact-SQL can only create some objects
in a self-contained batch, but that can
be done like this (which doesn't need it)
inside a longer batch:

EXEC (
N'
CREATE TABLE ##GlobalTemporary
    (
      Unnecessary varchar(100)
    )
')

SELECT * FROM ##GlobalTemporary
--X

However, any quote marks inside your 
object definition have to be doubled.

EXEC (N'PRINT ''Like this.'' ')

Down this path lie quote marks in fours,
in eights, and written as double-quote
characters "thus" to be substituted
later by REPLACE(... , '"', '''').
You don't start a project intending to 
do that!

You could use a similar technique to store 
object definitions in a table alongside tests, 
or inside temporary stored procedures, 
and then have a short script section that
retreives and calls and tests them however 
you like.

You could simply put all your tests in a 
separate script... but then you don't have 
your test code conveniently right next to 
the code for the object that is being tested.

You could do your object definitions plainly
in their own sections of script delimited
by GO, and only do something fancy-pants
for specifying the tests.

We bought an expensive tool called 
"Red Gate SQL Compare", which is pretty
good at doing things like synchronising 
new programs and other objects from 
development database to production -
and it can hand you a script of your changes
to run whenever you like (I don't think this 
comes with odd licence conditions).
But I don't think it can run your tests.

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

Thread

How to Get Local Variables That Are Not So Local Gene Wirchenko <genew@ocis.net> - 2012-05-29 19:55 -0700
  Re: How to Get Local Variables That Are Not So Local Erland Sommarskog <esquel@sommarskog.se> - 2012-05-30 07:20 +0000
    Re: How to Get Local Variables That Are Not So Local Gene Wirchenko <genew@ocis.net> - 2012-05-30 09:56 -0700
      Re: How to Get Local Variables That Are Not So Local Erland Sommarskog <esquel@sommarskog.se> - 2012-05-30 20:50 +0200
        Re: How to Get Local Variables That Are Not So Local Gene Wirchenko <genew@ocis.net> - 2012-05-30 13:31 -0700
          Re: How to Get Local Variables That Are Not So Local Erland Sommarskog <esquel@sommarskog.se> - 2012-05-31 07:23 +0000
  Re: How to Get Local Variables That Are Not So Local rja.carnegie@gmail.com - 2012-06-14 04:20 -0700
    Re: How to Get Local Variables That Are Not So Local Erland Sommarskog <esquel@sommarskog.se> - 2012-06-14 21:43 +0200
      Re: How to Get Local Variables That Are Not So Local Gene Wirchenko <genew@ocis.net> - 2012-06-14 14:58 -0700

csiph-web