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.
Your request is how to persist the variable across batches – not within a batch.
One way would be to use
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;