Use session_context to create a “variable” that lasts between batches.
15May 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 codeIf 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_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.
SQL Studies 
Beautiful! I had a developer implement this recently in a trigger and so far, we’ve been satisfied with the results. Great tip.
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.
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.)
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?
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! 🙂
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:
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.
Ha! Thanks for catching that. It’s fixed.
New Way to Page & Return Max Rows?
create function dbo.x (
@tr int
)
RETURNS int
AS
BEGIN
declare @TotR INT
if @tr = -1 begin
select @TotR = cast(session_context(N’TotRows’) as int)
end
else begin
EXEC sp_set_session_context N’TotRows’, @tr
select @TotR = @tr
end
return @TotR
end
go
DECLARE
@PageSize INT = 10,
@PageNum INT = 1,
@TotalRows INT;
EXEC sp_set_session_context N’TotRows’, 0
;WITH Data_CTE
AS
(
SELECT [name], object_id
FROM sys.all_objects
–where name =’x1′
),
Count_CTE
AS
(
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
FETCH NEXT @PageSize ROWS ONLY;
select dbo.x(-1)
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.”
Absolutely correct! And thanks for pointing it out. I’ll get it fixed right now.
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…
Good point!