Create a Variable that is declared for the length of a connection session?-CodePudding

I often test my SQL scripts in batches in a single script and have variable's declared at the beginning of the script to be used in multiple queries.

Is there a way to declare a variable that lasts, say, the length of the connection session so that I don't have to keep re-declaring/hard coding?

When testing queries individually, I either run the entire script up until the variable declaration, temporarily re-declare the variable in the portion I'm testing, or temporarily hard code the variable.

Declare @StartDate as Date = '20220701'
Declare @EndDate as Date = '20220707'

CodePudding user response：

Provided you're using SQL Server 2016 you can make use of session_context.

This provides working storage for the lifetime of your session (@@SPID) for key/value data pairs.

/* set a value */
exec sp_set_session_context 'MyName', 'my value';
    
/* retrieve a value */
select Session_Context('MyName');

See the documentation for details.

CodePudding user response：

One possibility that I've made use of is CONTEXT_INFO which holds data for the duration of the session or batch, and survives transaction rollback.

declare @contextInfo varbinary(128) = cast('Things and Stuff' as varbinary(128));
set context_info @contextInfo;
select cast(context_info() as varchar(128));

Results:

--------------------------------------------------------------------------------------------------------------------------------
Things and Stuff                                                                                                                

(1 row affected)