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.

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.

Starting from SQL 2016

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

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