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


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:

SET @Var1 = 1;
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.

-- 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:

  PRINT 'Jackpot!';
  EXEC sp_set_session_context 'COVID-19', 0;
  EXEC sp_set_session_context 'COVID-19', @i;

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:


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.


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.


The condition. Believe it or not sometimes this will be true.

  PRINT 'Jackpot!';
  EXEC sp_set_session_context 'COVID-19', 0;
  EXEC sp_set_session_context 'COVID-19', @i;

If SESSION_CONTEXT is the get then sp_set_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.

15 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, 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:

          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;
            PRINT 'Jackpot!';
            set context_info  0x0;
            set context_info @i_binary;
  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.

  4. David James says:

    New Way to Page & Return Max Rows?

    create function dbo.x (
    @tr int
    RETURNS int
    declare @TotR INT
    if @tr = -1 begin
    select @TotR = cast(session_context(N’TotRows’) as int)
    else begin
    EXEC sp_set_session_context N’TotRows’, @tr
    select @TotR = @tr
    return @TotR

    @PageSize INT = 10,
    @PageNum INT = 1,
    @TotalRows INT;

    EXEC sp_set_session_context N’TotRows’, 0

    ;WITH Data_CTE
    SELECT [name], object_id
    FROM sys.all_objects
    –where name =’x1′
    Select dbo.x((SELECT COUNT(*) AS TotalRows FROM Data_CTE)) x1
    SELECT Data_CTE.*
    FROM Data_CTE
    cross join Count_CTE
    where Count_CTE.x1>0
    ORDER BY [name]
    OFFSET (@PageNum – 1) * @PageSize ROWS

    select dbo.x(-1)

  5. Eliyahu Segal says:

    Great stuff!
    Small typo:
    “If SESSION_CONTEXT is the get then sp_get_session_context is the set.” shouldn’t it be:
    “If SESSION_CONTEXT is the get then sp_set_session_context is the set.”

  6. This is a very helpful feature when working in SSMS when working with ‘execute selected text’ If there is code you don’t want to run between where a variable is declared and the code you do want to run, being have to set the variables in session saves a lot of time, because you don’t have to move the declare statements around…

Leave a Reply

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

You are commenting using your 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,755 other subscribers

Follow me on Twitter

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