Browse All Articles > CONTEXT_INFO as a Table in SQL Server
BACKGROUND
To persist a small amount of data for the duration of a batch on a single connection or session (typically across multiple procedures and/or triggers), SQL Server provides the CONTEXT_INFO framework, a varbinary(128) value having connection scope. Typically you set this value by using the SET CONTEXT_INFO statement and retrieve it by selecting from the CONTEXT_INFO() function. This can be useful for sharing things like SCOPE_IDENTITY() values or user IDs between procedures and triggers where such information would otherwise be out of scope.
However, as-is CONTEXT_INFO can be rather cumbersome to work with in situations requiring the persistence more than one discreet value at a time.
ARTICLE SUMMARY
This article will demonstrate the ability to treat CONTEXT_INFO as a mutable table (technically a view) so that multiple values can be inserted, updated, selected, and deleted from CONTEXT_INFO as if it were a regular table. After implementing the steps outlined below, CONTEXT_INFO can be utilized as an ordered-pair table in the following manner:
The trick to this is simply to maintain CONTEXT_INFO as a delimited string that can be parsed and presented as a table (via a function), and modified by doing a little string manipulation in triggers on a view over that table.
Let's start by creating the foundational objects. First, we define two delimiter characters in scalar functions (a row delimiter and a field delimiter) so they can be used consistently across all of our objects. Any two characters can be used for this purpose, but I chose CHAR(2) and CHAR(3) respectively simply because these characters are unlikely to be used in the actual context data.
Some things to keep in mind as you experiment with this framework:
1. The insert/update/delete behaviors are simulated, and as such they don't work precisely the way they do on actual tables. For example, inserting the same Key more than once results in an update to the existing Key/Value in context, rather than multiple rows with the same Key.
2. Similarly, if an attempt is made to update the Key column itself, a new row is inserted instead containing the new Key. The old one must then be deleted in a separate statement. Remember there is no real constraint in place to keep the Key unique, so this must be managed by the calling application.
3. Also, attempts to update the Value column to an empty string or null results in the Key/Value pair being removed from context. This is by design.
4. Finally, keep in mind that behind the scenes we are only working with 128 bytes - including the field and row delimiters. This means at most 32 rows can be inserted, and then only if each row contains single-character Keys and Values. Likewise, a lone row with a single character Key can hold a Value of at most 125 bytes.
Later in this article I will demonstrate how this framework can be used to solve a common real-world problem. But first ...
BONUS MATERIAL
The insert/update/delete functionality is pretty cool (IMHO), but sometimes single-value functions are simpler to understand and work with. So presented below are a setter and a getter for putting individual Key/Value pairs into context. Data entered by either method can be accessed and manipulated by the other.
First the getter function:
USE CASE
CONTEXT_INFO is useful for passing data between procedures and triggers when such data in one would otherwise be out-of-scope in the other. This is illustrated in the following example scenario involving a Student table, a stored procedure, and a trigger.
First, let's create the table and a working stored procedure for saving the Student data:
Note that the output variable @StudentID is populated from the SCOPE_IDENTITY() function in the procedure (line 23 above). Also, GradeLevel remains null because it was not specified in the call to the procedure.
For the sake of example, let's add a trigger on the table to populate the GradeLevel if it is inserted as null:
See the problem? The GradeLevel was set in the trigger, but the output variable @StudentID is null. This is because the SCOPE_IDENTITY() function has the same scope as the actual insert, which was previously in the procedure until the trigger was added onto the student table.
CONTEXT_INFO can be used to work around this problem, so we modify the trigger and procedure accordingly. Alter the trigger to add the following at line 13:
Now alter the procedure by replacing line 23 with the following:
Now let's try again:
The world is whole again!
Another common scenario involves cases in which a trigger should behave differently depending on the value of some parameter to the procedure (a UserID, for example), but the UserID (in this example) is not a column on the table, and is therefore out of scope in the trigger. In such cases the UserID can be inserted into the ContextView prior to invoking the trigger, and the trigger can then retrieve it from the ContextView and act accordingly.
To persist a small amount of data for the duration of a batch on a single connection or session (typically across multiple procedures and/or triggers), SQL Server provides the CONTEXT_INFO framework, a varbinary(128) value having connection scope. Typically you set this value by using the SET CONTEXT_INFO statement and retrieve it by selecting from the CONTEXT_INFO() function. This can be useful for sharing things like SCOPE_IDENTITY() values or user IDs between procedures and triggers where such information would otherwise be out of scope.
However, as-is CONTEXT_INFO can be rather cumbersome to work with in situations requiring the persistence more than one discreet value at a time.
ARTICLE SUMMARY
This article will demonstrate the ability to treat CONTEXT_INFO as a mutable table (technically a view) so that multiple values can be inserted, updated, selected, and deleted from CONTEXT_INFO as if it were a regular table. After implementing the steps outlined below, CONTEXT_INFO can be utilized as an ordered-pair table in the following manner:
INSERT INTO ContextView ([Key],[Value])
SELECT 'MyUserID', '1234'
UNION
SELECT 'MyUserName', 'kEk100a'
UPDATE ContextView
SET [Value] = 'kEk1000a'
WHERE [Key] = 'MyUserName'
SELECT * FROM ContextView
DELETE FROM ContextView
WHERE [Key] = 'MyUserName'
SELECT * FROM ContextView
The trick to this is simply to maintain CONTEXT_INFO as a delimited string that can be parsed and presented as a table (via a function), and modified by doing a little string manipulation in triggers on a view over that table.
Let's start by creating the foundational objects. First, we define two delimiter characters in scalar functions (a row delimiter and a field delimiter) so they can be used consistently across all of our objects. Any two characters can be used for this purpose, but I chose CHAR(2) and CHAR(3) respectively simply because these characters are unlikely to be used in the actual context data.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[getContextFieldDelimiter]()
returns char
WITH SCHEMABINDING
as
begin
return char(2); -- ','
end
GO
create function [dbo].[getContextRowDelimiter]()
returns char
WITH SCHEMABINDING
as
begin
return char(3); -- '|'
end
GO
GO
create function [dbo].[getContextBuffer]()
returns varchar(128)
WITH SCHEMABINDING
as
begin
return rtrim( replace( convert(varchar(128),CONTEXT_INFO()), char(0), char(32) ));
end
GO
GO
CREATE function [dbo].[getContextTable]()
RETURNS @Context TABLE([Key] varchar(126), [Value] varchar(126))
WITH SCHEMABINDING
as
begin
declare @buffer varchar(128) = dbo.getContextBuffer();
declare @fPtr int = CHARINDEX(dbo.getContextFieldDelimiter(),@buffer);
declare @rPtr int = CHARINDEX(dbo.getContextRowDelimiter(),@buffer);
while @rPtr > 0
begin
insert into @Context
select SUBSTRING(@buffer,1,abs(@fPtr-1)), SUBSTRING(@buffer,@fPtr+1,@rPtr-@fPtr-1)
where @rPtr > nullif(@fPtr,0)+1
select @buffer = SUBSTRING(@buffer,@rPtr+1,128)
, @fPtr = CHARINDEX(dbo.getContextFieldDelimiter(),@buffer)
, @rPtr = CHARINDEX(dbo.getContextRowDelimiter(),@buffer)
end
return;
end
GO
GO
CREATE view [dbo].[ContextView]
WITH SCHEMABINDING
as
select top (32) [Key], [Value]
from dbo.getContextTable()
order by [Key]
GO
GO
CREATE TRIGGER [dbo].[tContextView_Delete]
ON [dbo].[ContextView]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
declare @buffer varchar(128) = '';
select @buffer += [Key] + dbo.getContextFieldDelimiter() + [Value] + dbo.getContextRowDelimiter()
from ContextView
where [Key] not in (select [Key] from deleted)
declare @varbin varbinary(128) = convert(varbinary(128),@buffer)
SET CONTEXT_INFO @varbin
END
GO
GO
CREATE TRIGGER [dbo].[tContextView_Insert]
ON [dbo].[ContextView]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
declare @buffer varchar(129)=''
select @buffer += [Key] + dbo.getContextFieldDelimiter() + [Value] + dbo.getContextRowDelimiter()
from ContextView
where [Key] not in (select [Key] from inserted where len([Key]) > 0) -- Exclude the values for these keys (if any) since we are about to change them.
select @buffer += ltrim(rtrim([Key])) + dbo.getContextFieldDelimiter() + max(ltrim(rtrim([Value]))) + dbo.getContextRowDelimiter()
from inserted
where LEN(ltrim(rtrim([Key]))) > 0
and LEN(ltrim(rtrim([Value]))) > 0
and LEN(@buffer) < 129
group by [Key] -- just to make sure each Key is unique
if LEN(@buffer) > 128
raiserror('Context buffer overflow',11,1);
declare @varbin varbinary(128) = convert(varbinary(128),@buffer)
SET CONTEXT_INFO @varbin
END TRY
BEGIN CATCH
declare @ErrMsg nvarchar(4000)=isnull(ERROR_MESSAGE(),'Error caught in ContextView'), @ErrSeverity int=ERROR_SEVERITY();
END CATCH
FINALLY:
if @ErrSeverity > 0 raiserror(@ErrMsg, @ErrSeverity, 1);
END
GO
Some things to keep in mind as you experiment with this framework:
1. The insert/update/delete behaviors are simulated, and as such they don't work precisely the way they do on actual tables. For example, inserting the same Key more than once results in an update to the existing Key/Value in context, rather than multiple rows with the same Key.
2. Similarly, if an attempt is made to update the Key column itself, a new row is inserted instead containing the new Key. The old one must then be deleted in a separate statement. Remember there is no real constraint in place to keep the Key unique, so this must be managed by the calling application.
3. Also, attempts to update the Value column to an empty string or null results in the Key/Value pair being removed from context. This is by design.
4. Finally, keep in mind that behind the scenes we are only working with 128 bytes - including the field and row delimiters. This means at most 32 rows can be inserted, and then only if each row contains single-character Keys and Values. Likewise, a lone row with a single character Key can hold a Value of at most 125 bytes.
Later in this article I will demonstrate how this framework can be used to solve a common real-world problem. But first ...
BONUS MATERIAL
The insert/update/delete functionality is pretty cool (IMHO), but sometimes single-value functions are simpler to understand and work with. So presented below are a setter and a getter for putting individual Key/Value pairs into context. Data entered by either method can be accessed and manipulated by the other.
First the getter function:
GO
CREATE function [dbo].[getContextValue](@Key varchar(126))
returns varchar(126)
WITH SCHEMABINDING
as
begin
return (
select Value
from dbo.ContextView
where [Key] = @Key
)
end
GO
GO
CREATE procedure [dbo].[setContextValue](@Key varchar(126), @Value varchar(126)=null)
as
BEGIN
BEGIN TRY
select @Key = rtrim(ltrim(@Key)), @Value = rtrim(ltrim(@Value))
if ISNULL(len(@Key),0) = 0
raiserror('Context Key may not by null or empty.',11,1)
declare @buffer varchar(128) = '';
select @buffer += [Key] + dbo.getContextFieldDelimiter() + [Value] + dbo.getContextRowDelimiter()
from ContextView
where [Key] != @Key -- Exclude the current value for this key (if it exists) since we are about to change it.
if LEN(@buffer) + LEN(@Key) + LEN(@Value) > 126
raiserror('Context buffer overflow.',11,1)
if ISNULL(len(@Value),0) > 0
select @buffer += dbo.getContextRowDelimiter() + @Key + dbo.getContextFieldDelimiter() + @Value
declare @varbin varbinary(128) = convert(varbinary(128),@buffer)
SET CONTEXT_INFO @varbin
END TRY
BEGIN CATCH
declare @ErrMsg nvarchar(4000)=isnull(ERROR_MESSAGE(),'Error caught in setContextValue'), @ErrSeverity int=ERROR_SEVERITY();
END CATCH
FINALLY:
if @ErrSeverity > 0 raiserror(@ErrMsg, @ErrSeverity, 1);
return isnull(len(@buffer),0);
END
GO
USE CASE
CONTEXT_INFO is useful for passing data between procedures and triggers when such data in one would otherwise be out-of-scope in the other. This is illustrated in the following example scenario involving a Student table, a stored procedure, and a trigger.
First, let's create the table and a working stored procedure for saving the Student data:
GO
CREATE TABLE [dbo].[tbl_Student](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](62) NOT NULL,
[LastName] [varchar](62) NOT NULL,
[BirthDate] [date] NOT NULL,
[GradeLevel] [tinyint] NULL,
CONSTRAINT [PK_tbl_Student] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
CREATE procedure [dbo].[usp_SaveStudent](
@StudentID int = null OUTPUT,
@FirstName varchar(62),
@LastName varchar(62),
@BirthDate date,
@GradeLevel tinyint = null
)
as
BEGIN
SET NOCOUNT ON;
declare @rc int = 0;
IF @StudentID IS NULL -- then insert a new student record
BEGIN
INSERT INTO tbl_Student (FirstName,LastName,BirthDate,GradeLevel)
SELECT @FirstName, @LastName, @BirthDate, @GradeLevel
SELECT @rc = @@ROWCOUNT
SELECT @StudentID = SCOPE_IDENTITY() -- get the new StudentID for output
END
ELSE -- update the existing record
BEGIN
UPDATE tbl_Student SET
FirstName = @FirstName,
LastName = @LastName,
BirthDate = @BirthDate,
GradeLevel = isnull(@GradeLevel,GradeLevel)
WHERE StudentID = @StudentID
SELECT @rc = @@ROWCOUNT
END
return @rc;
END -- usp_SaveStudent
GO
declare @StudentID int = null;
exec [usp_SaveStudent] @StudentID=@StudentID OUTPUT, @FirstName='John', @LastName='Doe', @BirthDate='2001-01-01'
select @StudentID as studentid
select * from tbl_Student
Note that the output variable @StudentID is populated from the SCOPE_IDENTITY() function in the procedure (line 23 above). Also, GradeLevel remains null because it was not specified in the call to the procedure.
For the sake of example, let's add a trigger on the table to populate the GradeLevel if it is inserted as null:
GO
create TRIGGER [dbo].[tStudentInsert]
ON [dbo].[tbl_Student]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO tbl_Student (FirstName,LastName,BirthDate,GradeLevel)
SELECT FirstName, LastName, BirthDate, ISNULL(GradeLevel,DATEDIFF(year,BirthDate,GETDATE())-5)
FROM inserted
END
GO
declare @StudentID int = null;
exec [usp_SaveStudent] @StudentID=@StudentID OUTPUT, @FirstName='Jane', @LastName='Doe', @BirthDate='2002-02-02'
select @StudentID as studentid
select * from tbl_Student
See the problem? The GradeLevel was set in the trigger, but the output variable @StudentID is null. This is because the SCOPE_IDENTITY() function has the same scope as the actual insert, which was previously in the procedure until the trigger was added onto the student table.
CONTEXT_INFO can be used to work around this problem, so we modify the trigger and procedure accordingly. Alter the trigger to add the following at line 13:
insert into ContextView
select 'New_Student_ID', SCOPE_IDENTITY()
Now alter the procedure by replacing line 23 with the following:
-- SELECT @StudentID = SCOPE_IDENTITY() -- get the new StudentID for output
SELECT @StudentID = [Value]
FROM ContextView
WHERE [Key] = 'New_Student_ID'
Now let's try again:
declare @StudentID int = null;
exec [usp_SaveStudent] @StudentID=@StudentID OUTPUT, @FirstName='Joe', @LastName='Doe', @BirthDate='2003-03-03'
select @StudentID as studentid
select * from tbl_Student
The world is whole again!
Another common scenario involves cases in which a trigger should behave differently depending on the value of some parameter to the procedure (a UserID, for example), but the UserID (in this example) is not a column on the table, and is therefore out of scope in the trigger. In such cases the UserID can be inserted into the ContextView prior to invoking the trigger, and the trigger can then retrieve it from the ContextView and act accordingly.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)