SQL Server – Associating Data with a Session

sessionsql serversql-server-2008

I think I want to associate a piece of data with each session.

The association must have the following properties:

  • The data being associated is provided either as a part of connection string (if that is possible at all) or as a parameter for a stored procedure which then must be the first SP called within the session.
  • The data must be easily available for any stored procedures the session then calls, without having this piece of data passed as a parameter.
    This point is essential for the idea: obviously, I could add a parameter into each of my stored procedures and oblige the software to pass the piece of data each time any stored proc is called, but that is exactly what I want to avoid.
  • The data must automatically expire and be invalid as soon as the session disconnects. No actions from the client must be required to mark the data as expired. If a session disconnects unexpectedly, that still must render its piece of data invalid.

I've considered several ways of doing it, but didn't like any:

  • Local temporary tables.
    Pick a table name (based on a freshly created GUID to avoid any clashes). Have a stored procedure that creates temporary table with this name and inserts a row with the data passed as a parameter. Then all other procs can reference the #table and get the data. The idea is that each session will have it's own #table, while the name of the #table remains the same.
    Won't work. The temporary table created in the stored procedure will be dropped upon exiting the procedure. Making the user create the #table directly, without calling a SP, is not an option.
  • A regular table where data is looked up by @SPID.
    Won't work. This requires deleting the data manually upon disconnect.
  • APP_NAME().
    Will probably work, but that's not really app name, and that will not be easily extendable.

Are there better ways of associating data with a session?

Best Answer

Use SET CONTEXT_INFO.

The most common use case for this is to send triggers information about the actor that caused the trigger to fire.

For example:

CREATE PROCEDURE [dbo].[ModifyData]
AS
BEGIN
    DECLARE @ObjectID BINARY(4) = CAST(@@PROCID AS BINARY(4));
    SET CONTEXT_INFO @ObjectID;

    UPDATE [dbo].[Data]
    ...
END;
GO

CREATE TRIGGER [LogModifications]
ON [dbo].[Data]
FOR UPDATE 
AS 
BEGIN
    DECLARE @ModifiedByObjectID INT = CAST(SUBSTRING(CONTEXT_INFO(), 1, 4) AS INT);

    ...
END;
GO
Related Question