Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Add Comment
Please, Sign In to add comment