The T-SQL PRINT statement lets you write to the Messages window in SQL Server Management Studio, which makes it a helpful aid when debugging SQL scripts and stored procedures.
Unfortunately there is one major issue with using PRINT. The messages output are only displayed when the query completes, so if you have a long-running query then you won’t be able to see any of the output until the end.
Here’s a quick example:
|
1 2 3 4 5 6 7 8 9 |
declare @i int set @i = 0 print 'Starting' while @i < 5 begin print @i waitfor delay '00:00:10' set @i += 1 end print 'Complete' |
Rubbish huh?
An alternative, and much better, approach is to use RAISERROR. This time the messages are printed immediately so you no longer have to wait for execution to complete to view them. Just be sure to use a non-fatal severity level – anything 10 or under is a good as it’s also unlikely to be picked up by any database monitoring systems.
|
1 2 3 4 5 6 7 8 9 10 11 |
declare @i int declare @message varchar(100) set @i = 0 RAISERROR('Starting', 10, 1) WITH NOWAIT while @i < 5 begin set @message = cast(@i as nvarchar(100)) RAISERROR(@message, 10, 1) WITH NOWAIT waitfor delay '00:00:10' set @i += 1 end RAISERROR('Complete', 10, 1) WITH NOWAIT |
As you can see, the logging syntax is slightly more verbose as RAISERROR takes a char/varchar but it’s a small price to pay for being able to see what’s going on as your queries run.
Pingback: BPOTW – 2015-05-15 | SQL Notes From The Underground