Monday, November 14, 2011

Use RAISERROR to return results in SSMS immediately

This courtesy of a coworker (JS), who found the idea online somewhere, possibly from Savjani here (http://blogs.msdn.com/b/sqlserverfaq/archive/2009/10/01/behavior-of-with-nowait-option-with-raiserror-in-sql-server.aspx)

If you run a while loop from SSMS, the results won't come back immediately, even if you're using a PRINT.  This is a clever way to return it immediately.


DECLARE @startdate DATETIME,
            @now DATETIME,
            @msg NVARCHAR(50)

SET @now = GETDATE()         
SET @startdate = GETDATE()- 60

WHILE (@startdate <= @now)
BEGIN
SET @msg = (select CONVERT(VARCHAR(10), @startdate, 101))
RAISERROR (@msg, 0, 1) WITH NOWAIT
SET @startdate = @startdate + 1
END