Use session_context to create a “variable” that lasts between batches.

10

May 26, 2020 by Kenneth Fisher

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 COVID-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 COVID-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.

10 thoughts on “Use session_context to create a “variable” that lasts between batches.

  1. natethedba says:

    Beautiful! I had a developer implement this recently in a trigger and so far, we’ve been satisfied with the results. Great tip.

  2. When introducing new things that didn’t exist in SQL Server 2000, could you please give at least passing mention to what version it was introduced in? Much appreciated, thanks!

    I live in Canada, and it’s a similar frustration when a website presumes that all clients are in the US, and I only learn I can’t benefit when it gets to the State field (no country) and the only states are US states (no provinces.) No heads-up that the service is only available in the US, but at that point it becomes obvious.

    • Generally if I know when something came into being I’ll mention it but a lot of times I just don’t know. I was told this was on the MCM exams so that puts it at at least SQL 2008/2008 R2.

      Honestly though, anything new being written you should assume that you’re going to have to test against SQL 2000 and that you’re probably going to be disappointed.

      • Daniel Bragg, SD says:

        In SQL Server 2014, I’m getting the message ‘SESSION_CONTEXT’ is not a recognized built-in function name. Putting ‘SESSION_CONTEXT’ into DuckDuckGo takes me to docs.microsoft.com/en-us/sql/t-sql/functions/session-context-transact-sql, which indicates that it was introduced in SQL 2016?

        (My at-home SSMS is currently 2014 because I can’t install anything newer due to dependency errors whenever I try, but that is an entirely different issue.)

        • Daniel Bragg, SD says:

          I only mention SQL 2000 as a “from the beginning of time” reference, not because I think it has any place installed on anyone’s machine any longer, and much less a business machine.

        • Fair enough. And I looked at the MS help entries and it does say 2016 and forward. That said, I was told that the idea at least was around well before that. I wonder if there were different commands then?

        • Daniel Bragg, SD says:

          Still, thanks for the post, as I had no idea that anything other than Table data (including temp tables) could persist across batches! Now, I’m off in search for a nail to beat with this new hammer! 🙂

        • Kev Riley says:

          Prior to SQL2016 you could use SET CONTEXT_INFO as the ‘set’ and CONTEXT_INFO() function as the get. There are some limitations with these, which is probably why they were superseded by SESSION_CONTEXT – Aaron Bertrand has more details here: https://www.mssqltips.com/sqlservertip/4094/phase-out-contextinfo-in-sql-server-2016-with-sessioncontext/

          I got this to work on a SQL2012 and SQL2014 instance:

          declare @i int
          declare @i_binary binary(128)
          
          select @i= isnull(CAST(context_info() as INT), 0) + 1;
          select @i_binary = cast(@i as binary(128));
          
          PRINT @i;
          
          IF SYSDATETIME() <> SYSDATETIME()
          BEGIN
            PRINT 'Jackpot!';
            set context_info  0x0;
          END
          ELSE
          BEGIN
            set context_info @i_binary;
          END;
  3. Brian Stork says:

    Interesting. I’d never heard of the session context. My only (and very minor) suggestion would be to correct your “coNvid-19” with “covid-19”. Just a little distracting.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,148 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: