How to Get Local Variables That Are Not So Local

[Gene Wirchenko]

Dear SQLers:

I have written a longish script. I am up to just over 800 lines,
and there is way more to come yet. In it, I define a number of stored
procedures. I have test code for most of them. My script looks
something like:
setup database
create tables and indexes
repeat n times
create procedure
test procedure
check table results
end off

I would like the option of easily turning off the testing.

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?

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
I have written a longish script. I am up to just over 800 lines,
and there is way more to come yet. In it, I define a number of stored
procedures. I have test code for most of them. My script looks
setup database
create tables and indexes
repeat n times
create procedure
test procedure
check table results
end off
I would like the option of easily turning off the testing.
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?

T-SQL is a language to query and modify relational database. For this it is
a very powerful language.

However, T-SQL is not a general-purpose language, and if you try to use it
for something it is not intended, you will have to accept the raw edges.

If you want a smoother ride, move the administrative tasks to the client-
side language of your choice.