SqlQuantumLeap

T-SQL Stored Proc to PRINT NVARCHAR(MAX) values

Jul 12th, 2016
611
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  2.  
  3. IF (OBJECT_ID(N'dbo.DisplayN') IS NULL)
  4. BEGIN
  5.   EXEC(N'CREATE PROCEDURE dbo.DisplayN AS SET NOCOUNT ON;');
  6. END;
  7.  
  8. GO
  9. /************************************************************************
  10.  Procedure:  DisplayN
  11.  Created by: Solomon Rutzky
  12.  Created on: 2016-07-12
  13.  Relates to: http://dba.stackexchange.com/questions/97188/how-to-output-more-than-4000-characters-in-sqlcmd/97350#97350
  14.  Version:    1.0
  15.  
  16.  Check the following URI for the most recent version: http://pastebin.com/4UBfFZRv
  17.  ************************************************************************/
  18. ALTER PROCEDURE dbo.DisplayN
  19. (
  20.   @TextToDisplay NVARCHAR(MAX),
  21.   @Debug BIT = 0
  22. )
  23. AS
  24. SET NOCOUNT ON;
  25.  
  26.   DECLARE @Length INT = LEN(@TextToDisplay),
  27.           @Buffer NVARCHAR(4000),
  28.           @BufferLength INT,
  29.           @StartIndex INT = 1,
  30.           @NextNewlineIndex INT,
  31.           @LineNumber INT = 0;
  32.  
  33.   SET @TextToDisplay = REPLACE(@TextToDisplay, NCHAR(13) COLLATE Latin1_General_100_BIN2, N''); -- normalize
  34.  
  35.   WHILE (1 = 1)
  36.   BEGIN
  37.     SET @Buffer = SUBSTRING(@TextToDisplay, @StartIndex, 4000);
  38.     SET @BufferLength = (DATALENGTH(@Buffer) / 2);
  39.  
  40.     IF (@Debug = 1)
  41.     BEGIN
  42.       SET @LineNumber += 1;
  43.       PRINT N'Line: ' + CONVERT(NVARCHAR(20), @LineNumber)
  44.             + N'; Chars: ' + CONVERT(NVARCHAR(20), @BufferLength)
  45.             + N'; Bytes: ' + CONVERT(NVARCHAR(20), DATALENGTH(@Buffer));
  46.  
  47.      IF (@BufferLength <> (DATALENGTH(@Buffer) / 2))
  48.      BEGIN
  49.         SELECT @Buffer AS [SourceText], CONVERT(VARBINARY(8000), @Buffer) AS [SourceBytes];
  50.      END;
  51.     END;
  52.  
  53.     IF (@BufferLength < 4000)
  54.     BEGIN
  55.       BREAK;
  56.     END;
  57.  
  58.     SET @NextNewlineIndex = CHARINDEX(NCHAR(10), @Buffer COLLATE Latin1_General_100_BIN2);
  59.  
  60.     IF (@NextNewlineIndex > 0)
  61.     BEGIN
  62.       PRINT SUBSTRING(@Buffer, 1, (@NextNewlineIndex - 1));
  63.       SET @StartIndex += @NextNewlineIndex;
  64.     END;
  65.     ELSE
  66.     BEGIN
  67.       PRINT @Buffer;
  68.       SET @StartIndex += @BufferLength;
  69.     END;
  70.   END; -- WHILE (1 = 1)
  71.  
  72.   -- Don't print empty line if final chunk was 4000 chars leaving final loop with 0
  73.   IF (DATALENGTH(@Buffer) > 0)
  74.   BEGIN
  75.     PRINT @Buffer;
  76.   END;
  77. GO
RAW Paste Data