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
#tableand get the data. The idea is that each session will have it’s own
#table, while the name of the
#tableremains 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
#tabledirectly, 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.
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?
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.
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