4

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 COMMIT or 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).

Improve this question
0

1 Answer 1

Reset to default
5

Every time you submit text from SSMS the batch will end when that finishes executing and any variables will go out of scope.

The only way to do this is using the debugger as illustrated below. Then you can step through line by line using the F10 or F11 keys. This should only be used in a development environment.

Also (as pointed out by @clifton_h) this feature has been removed from SSMS 18.x but is still available in the latest GA build (currently 17.9.1)

enter image description here

Improve this answer
2
  • Thanks Martin. I guess I never thought of my requirement as being "debugging" - but that is effectively what it is. For example, I want to check the result of a statement prior to continuing with a script. Eg - conducting a data fix I want to see the result is correct for one line before effecting logic on all data, so that I may back out if there is a problem (and my fix makes use of variables). I don't understand why debugging should avoid prod any more than a stepped-through transaction in prod, but ok.
    – youcantryreachingme
    Commented Feb 12, 2019 at 23:54
  • One question: if I end the debug session, what happens to data changes? Are they rolled back, or do they remain updated per the steps carried out during debug?
    – youcantryreachingme
    Commented Feb 12, 2019 at 23:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.