If I enter TSQL into SSMS Query editor, I know I can "step through" the statements (ie. execute them one at a time) by highlighting a statement and hitting F5 on the keyboard.
It seems by default though, that session does not have an open transaction. Every execution is committed.
I can write my TSQL so that it begins with
BEGIN TRAN and ends with a
ROLLBACK - and this ensures that as I step through, I have the option of backing out of changes.
However the issue I have is when I declare variables. Consider:
declare @flag bit = 0; select case when @flag = 0 then 'off' when @flag = 1 then 'on' end as FlagCheck;
Regardless of whether the above is wrapped up in a transaction, if I run the first statement on its own, then the next statement on its own, the second statement will fail because the variable has not been declared.
Is it possible to step through code like this and have SSMS understand that the effects of the earlier statement (declaring the variable, and assigning a value) should be carried over into the next step? (Again, even if wrapped in a transaction, this persistence of variables fails - even though the persistence of the data update is dependent on the transaction resolution - commit or rollback).