SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
IF (OBJECT_ID(N'dbo.DisplayN') IS NULL)
BEGIN
EXEC(N'CREATE PROCEDURE dbo.DisplayN AS SET NOCOUNT ON;');
END;
GO
/************************************************************************
Procedure: DisplayN
Created by: Solomon Rutzky
Created on: 2016-07-12
Relates to: http://dba.stackexchange.com/questions/97188/how-to-output-more-than-4000-characters-in-sqlcmd/97350#97350
Version: 1.0
Check the following URI for the most recent version: http://pastebin.com/4UBfFZRv
************************************************************************/
ALTER PROCEDURE dbo.DisplayN
(
@TextToDisplay NVARCHAR(MAX),
@Debug BIT = 0
)
AS
SET NOCOUNT ON;
DECLARE @Length INT = LEN(@TextToDisplay),
@Buffer NVARCHAR(4000),
@BufferLength INT,
@StartIndex INT = 1,
@NextNewlineIndex INT,
@LineNumber INT = 0;
SET @TextToDisplay = REPLACE(@TextToDisplay, NCHAR(13) COLLATE Latin1_General_100_BIN2, N''); -- normalize
WHILE (1 = 1)
BEGIN
SET @Buffer = SUBSTRING(@TextToDisplay, @StartIndex, 4000);
SET @BufferLength = (DATALENGTH(@Buffer) / 2);
IF (@Debug = 1)
BEGIN
SET @LineNumber += 1;
PRINT N'Line: ' + CONVERT(NVARCHAR(20), @LineNumber)
+ N'; Chars: ' + CONVERT(NVARCHAR(20), @BufferLength)
+ N'; Bytes: ' + CONVERT(NVARCHAR(20), DATALENGTH(@Buffer));
IF (@BufferLength <> (DATALENGTH(@Buffer) / 2))
BEGIN
SELECT @Buffer AS [SourceText], CONVERT(VARBINARY(8000), @Buffer) AS [SourceBytes];
END;
END;
IF (@BufferLength < 4000)
BEGIN
BREAK;
END;
SET @NextNewlineIndex = CHARINDEX(NCHAR(10), @Buffer COLLATE Latin1_General_100_BIN2);
IF (@NextNewlineIndex > 0)
BEGIN
PRINT SUBSTRING(@Buffer, 1, (@NextNewlineIndex - 1));
SET @StartIndex += @NextNewlineIndex;
END;
ELSE
BEGIN
PRINT @Buffer;
SET @StartIndex += @BufferLength;
END;
END; -- WHILE (1 = 1)
-- Don't print empty line if final chunk was 4000 chars leaving final loop with 0
IF (DATALENGTH(@Buffer) > 0)
BEGIN
PRINT @Buffer;
END;
GO
Comments