sqlsql-servervariablesswitching

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