- 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;
No comments:
Post a Comment
Note: only a member of this blog may post a comment.