When debugging SQL scripts, oftentimes the developer or DBA would want to see what is happening as the script executes. While it is possible to set up breakpoints in SSMS and step-into/-over code, sometimes all that one wants is to see the progression. The most common method of doing this is to use the
DECLARE @i INT = 0 PRINT 'Starting...' WHILE @i < 5 BEGIN SET @i = @i + 1 WAITFOR DELAY '0:00:01' PRINT @i END PRINT 'Done!'
Now, if you run this script, you'll get the following output:
Starting... 1 2 3 4 5 Done!
The problem is, you will only see this when the script finishes and all the
RAISERROR command. Note that while intended for raising errors, it will only break the execution and go into
CATCH block (if there is one) only if the severity provided to the command is greater than 10. So, let's replace the
RAISERROR, fire it up and see how the script outputs numbers in real-time:
DECLARE @i INT = 0 DECLARE @s VARCHAR RAISERROR('Starting...', 0, 0) WITH NOWAIT WHILE @i <= 5 BEGIN SET @i = @i + 1 WAITFOR DELAY '0:00:01' SET @s = CAST(@i AS VARCHAR) RAISERROR(@s, 0, 0) WITH NOWAIT END RAISERROR('Done!', 0, 0) WITH NOWAIT