How SESSION_CONTEXT() Works in SQL Server

In SQL Server, you can use the SESSION_CONTEXT() function to read the value of a specified key in the current session context.

The key (key/value pair) needs to be set first. This can be done with the sp_set_session_context stored procedure.

Once a key/value pair has been set for the session, you can use SESSION_CONTEXT() to return that key’s value.

Example 1 – Set & Return the Value

Here’s an example that demonstrates the basic concept and usage.

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

SELECT SESSION_CONTEXT(N'user_id') AS user_id;

Result:

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

Example 2 – When a Key Doesn’t Exist

Here’s what happens if you try to get the value from a key that doesn’t exist.

SELECT SESSION_CONTEXT(N'oops') AS oops;

Result:

+--------+
| oops   |
|--------|
| NULL   |
+--------+

Example 3 – The “N” Prefix

The argument provided to SESSION_CONTEXT() is of type sysname. Basically, this is the same as nvarchar(128) NOT NULL, which means you need to prefix the argument with the N character.

Here’s what happens if I remove the N prefix:

EXEC sp_set_session_context
  @key = 'language',
  @value = 'English';

SELECT SESSION_CONTEXT('language') AS language;

Result:

Msg 8116, Level 16, State 1, Line 5
Argument data type varchar is invalid for argument 1 of session_context function.

By removing the N prefix, I’m simply passing in a varchar, when it should be nvarchar (or sysname to be exact).

Here it is with the N prefix:

EXEC sp_set_session_context
  @key = N'language',
  @value = 'English';

SELECT SESSION_CONTEXT(N'language') AS language;

Result:

+------------+
| language   |
|------------|
| English    |
+------------+

Example 4 – The Return Value

The return type of SESSION_CONTEXT() is sql_variant.

You can use the SQL_VARIANT_PROPERTY() function to find out the base type.

Example:

SELECT 
  SQL_VARIANT_PROPERTY(
      SESSION_CONTEXT(N'user_id'), 'BaseType'
      ) AS user_id,
  SQL_VARIANT_PROPERTY(
      SESSION_CONTEXT(N'language'), 'BaseType'
      ) AS language;

Result:

+-----------+------------+
| user_id   | language   |
|-----------+------------|
| int       | varchar    |
+-----------+------------+

Example 5 – Concatenating Return Values

If you need to concatenate multiple results, you’ll need to convert the results to a data type other than sql_variant first.

Here’s an example of what happens if I don’t do this:

Example:

EXEC sp_set_session_context N'user_fname', 'Homer';
EXEC sp_set_session_context N'user_lname', 'Simpson';

SELECT 
  CONCAT(
      SESSION_CONTEXT(N'user_fname'), 
      SESSION_CONTEXT(N'user_lname')
      ) AS Result;

Result:

Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.

So I need to explicitly convert the results using either CAST() or CONVERT() before concatenating them.

Example:

EXEC sp_set_session_context N'user_fname', 'Homer';
EXEC sp_set_session_context N'user_lname', 'Simpson';

SELECT 
  CONCAT(
      CAST(SESSION_CONTEXT(N'user_fname') AS varchar(5)),
      CAST(SESSION_CONTEXT(N'user_lname') AS varchar(7))
      ) AS Result;

Result:

+--------------+
| Result       |
|--------------|
| HomerSimpson |
+--------------+