Skip to content
Advertisement

Set a local variable and use throughout the query batch?

Once I insert default values in the table I store the result of scope identity in a variable

insert into OrderPlaced default values;
declare @id bigint;
set @id = SCOPE_IDENTITY();

After this, I have to run some other pieces of code that change the value of scope identity and after running those pieces of code I have to use the value of @id again but it shows an error saying that I must declare the variable which I have already done above.

EXEC dbo.GetRecieptById @ID = @id;

Unfortunately, I can’t just select the whole code block and execute it at once as this is for a presentation and I have to show each individual steps.

Advertisement

Answer

Your request is how to persist the variable across batches – not within a batch.

One way would be to use SESSION_CONTEXT

declare @id bigint;
insert into OrderPlaced default values;
set @id = SCOPE_IDENTITY();
EXEC sys.sp_set_session_context  @key= N'@id',@value = @id 

GO

declare @id bigint = CAST(SESSION_CONTEXT(N'@id') AS BIGINT)
EXEC dbo.GetRecieptById @ID = @id;
1 People found this is helpful
Advertisement