Monday, 10 August 2015

MSSQL - Immediately PRINT big messages - more then 8000 bytes (4000 unicode characters)

Transact-SQL function PRINT has two disadvatages:
- is limited for 8000 bytes - 8000 VARCHAR characters or 4000 NVARCHAR (unicode) characters
- does not send messages immediately to the client, usually user have to wait until the procedure is complete before seeing messages

We can use alternate method - the RAISERROR function, this function can send messages immediately, but is limited for 2047 characters.

There the stored procedure which send any messages immediately to client, also with length more then 2047 (or 8000) characters:
CREATE PROCEDURE [dbo].[sp_print_big_message]
    @message NVARCHAR(MAX)
AS BEGIN

    -- SET NOCOUNT ON to prevent extra log messages
    SET NOCOUNT ON;

    DECLARE
        @CRLF            CHAR(2) = CHAR(13)+CHAR(10),
        @message_len    INT = LEN(@message),
        @i                INT,
        @part            NVARCHAR(2000),
        @part_len        INT;

    IF @message_len <= 2000 BEGIN
        -- Message ist enough short
        RAISERROR (@message, 0,1) WITH NOWAIT;
    END ELSE BEGIN
        -- Message is too long
        SET @i = 1;
        WHILE @i < LEN(@message) BEGIN
            -- Split to parts end send them to client immediately
            SET @part = SUBSTRING(@message, @i, 2000);
            SET @part_len = 2000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(@part)) - 1;
            SET @part = CASE @i
                            WHEN 1
                            THEN ''
                            ELSE '/* CRLF ' + CAST(@i AS VARCHAR(20)) + ':'
                                 + CAST(@part_len AS VARCHAR(20)) + ' */' + @CRLF
                        END
                        + REPLACE(SUBSTRING(@message, @i, @part_len), '%', '%%');
            RAISERROR (@part, 0,1) WITH NOWAIT;
            SET @i = @i + @part_len + 2;
        END;
    END;

END;

Usage:
-- Declare long message
DECLARE @LongMessage NVARCHAR(MAX) = '';
-- Fill message with test data
DECLARE @i INT = 1;
WHILE @i < 200 BEGIN
    SET @LongMessage = @LongMessage
                       + CASE @i WHEN 1 THEN '' ELSE CHAR(13) + CHAR(10) END
                       + CAST(@i AS VARCHAR(10))
                       + '. Lorem ipsum dolor sit amet, consectetur adipiscing elit.';
    SET @i = @i + 1;
END;

-- Display length of generated message
DECLARE @len INT = LEN(@LongMessage);
RAISERROR('Message length: %i', 0, 1, @len);

-- Use SP to print long message
EXEC sp_print_big_message @LongMessage;