[SOLVED] persisted switching variables in long SQL script?

persisted switching variables in long SQL script?

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 ?

Solution

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