Temporary Stored Procedures: Outputting Status in Scripts

On another connection (on another computer for that matter), I am right no doing some pretty long loads of some test data. The script is comprised of 6 queries, and they each may take 10 minutes (not completely sure, this is my first run of the scripts). And of course, I want to get some feedback on these queries to know how long they are taking.

One common way to do this is to put a PRINT statement between the queries so you can see the progress. But PRINT statements are notorious for one thing. Caching the output until the output buffer reaches some level.

Annoyingly, when I asked google this question, I couldn’t find any article to reference in the output, but the AI gave me the answer I knew to be true. My references (subject to change).

Immediate Output Messages

To get around this buffering limitation, you need something to cause the buffer to flush. There are a few typical examples, the first being a query result (for example, not just a rowcount output), the other being an error that stops the batch. If you want to cause it to flush, you can use RAISERROR with the NOWAIT parameter.

So you can do the following with RAISERROR, and the NOWAIT parameter causes the message to be output along with any other messages that are in the buffer:

RAISERROR ('this is a message',10,1) WITH NOWAIT;

Any severity under 10 will spit out an unformatted message like:

this is a message

But if you do 11 or higher, it will be considered an error:

RAISERROR ('this is an error',11,1) WITH NOWAIT;

Msg 50000, Level 11, State 1, Line 21
this is an error

Severity 16 is where true “errors” start, and you can read more in the documentation. The most important thing for this article is that we want to just want to put out a message that happens immediately while a query is taking a while, so we know what part of a script is executing.

Technique for putting out message

One thing I have commonly done is to create something like this, with a block for the start of the process, and then one for the end:

DECLARE @message nvarchar(1000) = 'Process X has started';
SET @message = CONCAT(@Message , 
      ' : Message Time - ', CAST(SYSDATETIME() AS VARCHAR(100)) );
RAISERROR(@message,10,1) WITH NOWAIT;

--Process X happens

SET @message = 'Process X has finished'
SET @message = CONCAT(@Message , 
     ' : Message Time - ', CAST(SYSDATETIME() AS VARCHAR(100)) );
RAISERROR(@message,10,1) WITH NOWAIT;

Which works great:

Process X has started : Message Time - 2025-10-21 13:27:28.8318894
Process X has finished : Message Time - 2025-10-21 13:27:28.8318894

Bundling this repeated code

The message concept is very cool, and quite useful. But what if you do this pretty often, like 5 or 10 times in a script? It gets tedious, very tedious (especially with variables you have to manage). Plus, what if I want other bits of common information too?

So, I decided to write a stored procedure that could bundle the calls together so that outputting this data would be easy. It also lets me build a set of parameters/values that I may want to use but may not in all cases. And I don’t want to put this code in every database where I might use it, so I found that a temporary stored procedure fits the bill nicely.

In the next bit of code, I create a reusable bit of code for places where I am creating a script of code that I want to separate with something that looks like a PRINT statement.

CREATE OR ALTER PROCEDURE #OutputMessageNOW
  @Message nvarchar(1000) = 'Default Message', --set null if not needed
  @AddTimeToMessageFlag bit = 1, --adds system time to the message
  @AddSpidToMessageFlag bit = 0, --add spid to the message
  @AddOriginalLoginToOutputFlag bit = 0 --add the user who is logged in
AS
BEGIN
  SET @message = CONCAT('',@Message + ' : ',
          'Message Time - ' + CASE WHEN @AddTimeToMessageFlag = 1 
              THEN CAST(SYSDATETIME() AS varchar(100)) 
              ELSE NULL END + ' : ',
          'ProcessId - ' + CASE WHEN @AddSpidToMessageFlag = 1 
              THEN CAST(@@spid AS varchar(100)) 
              ELSE NULL END + ' : ',
          'LoggedInUserId - ' + CASE WHEN @AddOriginalLoginToOutputFlag = 1 
              THEN original_login() ELSE NULL END + ' : '); 

  SET @Message = SUBSTRING(@Message,1,len(@message) - 2)
  RAISERROR(@message,10,1) WITH NOWAIT; END;
GO

Note that I have implemented a number of extra values in the stored procedure, because they are things that I commonly need when I am spitting out these values for a script or a test. I could also see adding things like @@trancount or other values if you ever need them or something that is specific to your organization.

A lot of examples

Here are a good subsection of examples for how this would work. By default, with no input:

EXEC #outputmessageNOW;

You get the following output, good for the simplest of cases (and sometimes when I want to see how long something is taking, I can just that call and get the time of the execution:

Default Message : Message Time - 2025-10-21 13:40:39.6213337

Or you can throw in a message, which is the typical use case:

EXEC #outputmessageNOW 'This step is complete';

Now the message is customized.

This step is complete : Message Time - 2025-10-21 13:41:32.5592584

If you just want strictly the time:

EXEC #outputmessageNOW @Message = NULL;

This outputs:

Message Time - 2025-10-21 13:41:59.1955306

If you just want the processId (SPID for short…and for us old timers).

EXEC #outputmessageNOW @Message = NULL, @AddTimeToMessageFlag = 0, 
            @AddSpidToMessageFlag = 1

And this just gives the processId:

ProcessId - 68

And to get the user that is logged in:

EXEC #outputmessageNOW @Message = NULL, @AddTimeToMessageFlag = 0,
     @AddSpidToMessageFlag = 0, @AddOriginalLoginToOutputFlag = 1

This will give you the user who logged in:

LoggedInUserId - Domain\Username

Or the whole kit and kaboodle:

EXEC #outputmessageNOW @Message = 'Custom Message', 
   @AddTimeToMessageFlag = 1, @AddSpidToMessageFlag = 1, 
   @AddOriginalLoginToOutputFlag = 1;

The message, the time, the processid and logged In user (note, I added the wrap to the text):

Custom Message : Message Time - 2025-10-21 13:48:55.5785636 : ProcessId - 68 :
 LoggedInUserId - MicrosoftAccount\drsql@hotmail.com

Now, just paste this into your script at the start and go.

For me, I am going to use Redgate SQL Prompt’s Snippet capabilities so I can have it at my fingertips, but you can just keep the script around and insert it into your script however you wish.

You can see how it works in a typical setting using the following. And yes, that is a lot of code to add to your script… but it only needs to be repeated the one time, so it is actually less code than you might typically need.:

CREATE OR ALTER PROCEDURE #OutputMessageNOW
  @Message nvarchar(1000) = 'Default Message', --set null if not needed
  @AddTimeToMessageFlag bit = 1, --adds system time to the message
  @AddSpidToMessageFlag bit = 0, --add spid to the message
  @AddOriginalLoginToOutputFlag bit = 0 --add the user who is logged in
AS
BEGIN
  SET @message = CONCAT('',@Message + ' : ',
          'Message Time - ' + CASE WHEN @AddTimeToMessageFlag = 1 
              THEN CAST(SYSDATETIME() AS varchar(100)) 
              ELSE NULL END + ' : ',
          'ProcessId - ' + CASE WHEN @AddSpidToMessageFlag = 1 
              THEN CAST(@@spid AS varchar(100)) 
              ELSE NULL END + ' : ',
          'LoggedInUserId - ' + CASE WHEN @AddOriginalLoginToOutputFlag = 1 
              THEN original_login() ELSE NULL END + ' : '); 

  SET @Message = SUBSTRING(@Message,1,len(@message) - 2)
  RAISERROR(@message,10,1) WITH NOWAIT; END;
GO
EXEC #OutputMessageNOW 'Starting script';

WAITFOR DELAY '00:00:10' --10 seconds

EXEC #OutputMessageNOW 'First step done';

WAITFOR DELAY '00:00:10' --10 seconds'

EXEC #OutputMessageNOW 'Completed';

The output will be something like the following:

Starting script : Message Time - 2025-10-21 14:01:12.5902020
First step done : Message Time - 2025-10-21 14:01:22.5910528
Completed : Message Time - 2025-10-21 14:01:32.5924262

Summary

In this blog I have presented a tool that you can use to add to your scripts to put out immediate messages to the client so you can watch the process of a script more easily. Even with no parameters, it could output a nice little message to break up some output, and give you a bit of guidance as to how long each step has taken.

By just pasting a simple bit of code into the script, a temporary stored procedure can then condense a set of repeated code into a far simpler call that you may make multiple times, without having to worry about things like variable declarations and such clashing with the rest of your code.

Drsql.link