The other day I had a brief conversation with Itzik Ben-Gan (about|twitter) on twitter and I have to tell you the man is amazing. In a very brief exchange where he tossed a few T-SQL scripts at me, my brain hurt and I learned some really interesting stuff. In particular he showed me how to pass a variable between batches in the same session.
Let me explain that briefly. A session is basically a connection. So you create a connection to SQL and until you close it that is a session. A batch is essentially an execution of code up to a batch separator, which is usually GO. In very simple terms the scope (how long they last) of a variable is at most the batch they were declared in. Because I feel like I can usually explain better with an example:
DECLARE @Var1 INT; SET @Var1 = 1; GO PRINT @Var1;
If this whole thing is run you’ll get an error telling you that the variable @Var1 doesn’t exist, because after the GO it’s a new batch and the variable doesn’t, in fact, exist anymore.
DECLARE @Var1 INT; -- bunch of code here -- Highlight and execute from here up -- Highlight and execute from here down -- more code SET @Var1 = 1; -- yet more code
If I highlight and execute a section of code that includes the SET @Var1 but not the DECLARE @Var1 then I’m going to get the same error, because regardless of if I ran the DECLARE earlier it doesn’t exist in this particular execution of the code.
If you’ve worked much with T-SQL, or heck any type of coding, you’ve dealt with the problem of variables and their scope. And you’ve probably run into a desire to have a variable that persists between batches. And while I don’t know about other languages it turns out there is a way to set a “variable” that persists through the entire session in T-SQL. Here is the code that Itzik sent me:
DECLARE @i AS INT = ISNULL(CAST(SESSION_CONTEXT(N'COVID-19') AS INT), 0) + 1; PRINT @i; IF SYSDATETIME() <> SYSDATETIME() BEGIN PRINT 'Jackpot!'; EXEC sp_set_session_context 'COVID-19', 0; END ELSE BEGIN EXEC sp_set_session_context 'COVID-19', @i; END;
A very basic description of what this does. It sets a variable, tests for a condition and then if the condition is true it prints Jackpot!. If the condition is false it increments the variable. The idea being to see how many times you can hit F5 (execute) without the condition being true. If SESSION_CONTEXT (which I will explain in a sec) wasn’t used to store the contents of the variable, then each time you ran this code the variable would be a 1 which would kind of defy the purpose.
Starting at the top:
DECLARE @i AS INT = ISNULL(CAST(SESSION_CONTEXT(N’COVID-19') AS INT), 0) + 1;
Starting in the center with the star of the show SESSION_CONTEXT. This system function gets the value of a session key (in this case CONVID-19) currently stored for this session. Be warned, SESSION_CONTEXT returns a sql_variant. Basically, like I said above, it’s a variable scoped for the session (i.e. it will last as long as the session does). The rest of this statement declares the variable @i and sets it to the contents of the session key CONVID-19 +1. If the session key is NULL (or not set) the the variable is set to 0 + 1.
PRINT @i;
I hope I don’t have to explain this much right? Print the contents of @i so we know how many times we’ve run the code so far.
IF SYSDATETIME() <> SYSDATETIME()
The condition. Believe it or not sometimes this will be true.
BEGIN PRINT 'Jackpot!'; EXEC sp_set_session_context 'COVID-19', 0; END ELSE BEGIN EXEC sp_set_session_context 'COVID-19', @i; END;
If SESSION_CONTEXT is the get then sp_get_session_context is the set. You use this function to create and set the session key. In this case if the condition was true then we are resetting it back to 0 and if false then setting it to the incremented value (remember it was incremented in the first line of code). There doesn’t appear to be any difference between setting the value of a session key that already exists and creating a new one and setting the value to it.
Also just an FYI you can create these with a read_only flag.