<

CONTEXT_INFO as a Table in SQL Server

Published on
10,949 Points
4,849 Views
1 Endorsement
Last Modified:
Approved
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:

   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

Open in new window

IMPLEMENTATION

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

Open in new window

Next, another UDF to simplify the retrieval and conversion of the raw CONTEXT_INFO data into a string:

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

Open in new window

Now to the heart of the matter - a function to parse our doubly-delimited string and return it as a table.  

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

Open in new window

Next, a user-friendly View of this table:

GO
CREATE view [dbo].[ContextView]
  WITH SCHEMABINDING
as
select top (32) [Key], [Value]
from dbo.getContextTable()
order by [Key]
GO

Open in new window

And this is where the magic happens - the triggers to format and manipulate the context string.  The simplest of the two is the DELETE trigger:

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

Open in new window

Then the combined insert/update trigger, which is a bit more involved but still rather simple:

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

Open in new window

Now we are ready to run the sample queries from the ARTICLE SUMMARY section (above)!

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

Open in new window

The setter is implemented as a stored procedure because CONTEXT_INFO cannot be set in a UDF.

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

Open in new window


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

Open in new window


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

Open in new window

Now let's run the procedure to create a record:

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

Open in new window


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

Open in new window

Now let's create another Student record:

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

Open in new window


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()

Open in new window


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'

Open in new window


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

Open in new window


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.
1
Comment
0 Comments

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Join & Write a Comment

Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month