- 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.