CODEWITHSUNDEEP
sqlsql-servert-sql
liserdarts
liserdarts

Reputation: 260

SQL session/connection variables

I'm trying to find some equivalent to session variables in SQL. I want to be able to store and retrieve just a number but each connection to the database has a different number. It needs to persist from one batch to the next on the same connection.

I did have a solution that used a global cursor like this.

IF (SELECT CURSOR_STATUS('global','ChangeSet')) >= 0 
BEGIN --Close and deallocate the cursor
    Close ChangeSet
    DEALLOCATE ChangeSet
END

--Create a new cursor
DECLARE ChangeSet CURSOR GLOBAL STATIC FOR
SELECT ChangeSet = @ChangeSet

--Open the cursor
OPEN ChangeSet

Each connection would have a different cursor so it worked, but this is not usable inside of a view. I guess if somebody can show me how to read this in a view that would be cool too.

I'm using MS SQL Server btw.

Upvotes: 4

Views: 10416

Answers (3)

Juhan Jalutaja
Juhan Jalutaja

Reputation: 21

Starting from SQL 2016

EXEC sys.sp_set_session_context @key = N'language', @value = 'English';  
SELECT SESSION_CONTEXT(N'language');

Upvotes: 2

Andomar
Andomar

Reputation: 238176

A temporary table survives a batch (including go). It's still connection specific:

create table #temp (val float)
insert #temp values (3.14)
go
select * from #temp

Upvotes: 0

Ed Harper
Ed Harper

Reputation: 21505

The CONTEXT_INFO property may be what you're looking for - it enables you to set and read a connection-specific binary value.

You could encode your numeric value to binary and store it in this property.

Upvotes: 6

Related Questions