In SQL Server, you can use the sp_set_session_context stored procedure to set key/value pairs in the session context.

These key/value pairs then remain in your session context until your connection to SQL Server closes. You can use these values in stored procedures and other T-SQL code throughout the life of your session.

This method for maintaining session state was first introduced in SQL Server 2016. Prior to that release, you would need to use SET CONTEXT_INFO, which provides a similar, but much more limited way of storing session state.

Example 1 – Set the Value

Here’s an example to demonstrate how to use sp_set_session_context to set a key/value pair.

EXEC sp_set_session_context
  @key = N'user_id',
  @value = 15;

You could also use the following syntax:

EXEC sp_set_session_context 'user_id', 15;

There’s also an optional read-only flag that you can set. See below for an example.

Example 2 – Read the Value

You can use the SESSION_CONTEXT() function to read the value of a key.

Here’s how to read the value I set in the previous example.

SELECT SESSION_CONTEXT(N'user_id') AS user_id;

Result:

+-----------+
| user_id   |
|-----------|
| 15        |
+-----------+

Example 3 – Update the Value

You can update the value, as long as you haven’t specified it as read-only.

EXEC sp_set_session_context 'user_id', 73;
SELECT SESSION_CONTEXT(N'user_id') AS user_id;

Result:

+-----------+
| user_id   |
|-----------|
| 73        |
+-----------+

Example 4 – Set to Read-Only

As mentioned, there’s also an optional read-only flag that you can set. A value of 1 sets it to read-only, and a value of 0 (the default) is not read-only.

If you set it to read-only, you won’t be able to update the value without disconnecting and reconnecting to SQL Server.

Here’s an example of setting the read-only flag.

EXEC sp_set_session_context 
  @key = N'user_id', 
  @value = 15, 
  @read_only = 1;
SELECT SESSION_CONTEXT(N'user_id') AS user_id;

Result:

+-----------+
| user_id   |
|-----------|
| 15        |
+-----------+

Now, let’s try to change the value:

EXEC sp_set_session_context 'user_id', 73;

Result:

Msg 15664, Level 16, State 1, Line 1
Cannot set key 'user_id' in the session context. The key has been set as read_only for this session.