persisted switching variables in long SQL script?

Question

I am using switching variables in a long SQL script that I want to run in SSMS in a half manual way. I mean, selecting some parts of the script and executing thoses instructions in batches.

The switching variables at the start of the script look like:

--start of script 
DECLARE @CompanySwitch bit
DECLARE @CompanyA bit=0
DECLARE @CompanyB bit=1
Set @CompanySwitch=@CompanyA

Then, lots of long queries, bla bla ...

...

Once in a while, many lines down the script, the switching variables are evaluated to select some behavior:

--middle of script (2000 lines further down)
SELECT CASE @CompanySwitch WHEN @CompanyA THEN 'titi' WHEN @CompanyB THEN 'toto' END AS UsingSwitchingVariables

If I try to execute the SELECT CASE in isolation, after having executed the switching variable declarations also in isolation, I get Err Msg 137: "scalar variable @CompanySwitch must be declared".

To make it work, I need to copy the switching variable declaration code over to the beginning of the code batch that I want to execute, and execute both (declaration and use) in one batch:

DECLARE @CompanySwitch bit
DECLARE @CompanyA bit=0
DECLARE @CompanyB bit=1
Set @CompanySwitch=@CompanyA
SELECT CASE @CompanySwitch WHEN @CompanyA THEN 'titi' WHEN @CompanyB THEN 'toto' END AS UsingSwitchingVariables

Not very handy! Is there a way to create switching variables that can be remembered across executions ?

Why not just put your values in a temp table that will remain for the duration of your session? — Stu, Jun 07 '21 at 15:50
You could store the values in a staging/temp table and select them from there each time. Then that way you are not having to declare variables (which is your issue), otherwise I do not believe so. I had had similar situations and I just would copy the declare statements to each part I needed and comment them out and only select/uncomment when I needed to execute just that part like you have. — Brad, Jun 07 '21 at 15:50
Short answer - no. Any variable used within a batch must be declared within that same batch. — SMor, Jun 07 '21 at 15:59
Consider using a CTE of the form `WITH query1 AS (...), query2 AS (...) ...` for each stage, then the final `SELECT` can get the results of each stage without having to carefully select statements. This is of course assuming the batch consists only of `SELECT`s and not some data modification in between. — Jeroen Mostert, Jun 07 '21 at 16:08
OK, thx, I guess I 'll create a table to hold the values. It will take a table creation and a join for each use. — Ludovic Aubert, Jun 07 '21 at 16:15

score 1 · Accepted Answer · answered Jun 07 '21 at 18:53

DROP TABLE IF EXISTS switch;

CREATE TABLE switch(
    id integer primary key,
    company varchar(20),
    CHECK(company='CompanyA' OR company='CompanyB'),
    CHECK(id=1)
);
INSERT INTO switch VALUES(1,'CompanyA');

And wherever you need the switch:

SELECT CASE company 
  WHEN 'CompanyA' THEN 'titi' 
  WHEN 'CompanyB' THEN 'toto' 
 END AS UsingSwitchingVariables
FROM ...
CROSS JOIN switch

persisted switching variables in long SQL script?

1 Answers1