How to print VARCHAR(MAX) using Print Statement?

I have a code which is:

DECLARE @Script VARCHAR(MAX)

SELECT @Script = definition FROM manged.sys.all_sql_modules sq
where sq.object_id = (SELECT object_id from managed.sys.objects 
Where type = 'P' and Name = 'usp_gen_data')

Declare @Pos int

SELECT  @pos=CHARINDEX(CHAR(13)+CHAR(10),@script,7500)

PRINT SUBSTRING(@Script,1,@Pos)

PRINT SUBSTRING(@script,@pos,8000)

The length of the Script is around 10,000 Characters and Since I am using print Statement which can hold only max of 8000. So I am using two print statements.

The problem is when I have a script which is of say 18000 characters then I used to use 3 print statements.

So Is there a way that I could set the number of print statements depending on the length of the script?

I know it's an old question, but what I did is not mentioned here.

For me the following worked.

DECLARE @info NVARCHAR(MAX)

--SET @info to something big

PRINT CAST(@info AS NTEXT)

The following workaround does not use the PRINT statement. It works well in combination with the SQL Server Management Studio.

SELECT CAST('<root><![CDATA[' + @MyLongString + ']]></root>' AS XML)

You can click on the returned XML to expand it in the built-in XML viewer.

There is a pretty generous client side limit on the displayed size. Go to Tools/Options/Query Results/SQL Server/Results to Grid/XML data to adjust it if needed.

Here is how this should be done:

DECLARE @String NVARCHAR(MAX);
DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
DECLARE @offset tinyint; /*tracks the amount of offset needed */
set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@String) > 1
BEGIN
    IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN
           SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
    END
    ELSE
    BEGIN
           SET @CurrentEnd = 4000
            set @offset = 1
    END   
    PRINT SUBSTRING(@String, 1, @CurrentEnd) 
    set @string = SUBSTRING(@String, @CurrentEnd+@offset, LEN(@String))   
END /*End While loop*/

Taken from http://ask.sqlservercentral.com/questions/3102/any-way-around-the-print-limit-of-nvarcharmax-in-s.html

You could do a WHILE loop based on the count on your script length divided by 8000.

EG:

DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@script) / 8000) + 1
WHILE @Counter < @TotalPrints 
BEGIN
    -- Do your printing...
    SET @Counter = @Counter + 1
END

Came across this question and wanted something more simple... Try the following:

SELECT [processing-instruction(x)]=@Script FOR XML PATH(''),TYPE

This proc correctly prints out VARCHAR(MAX) parameter considering wrapping:

CREATE PROCEDURE [dbo].[Print]
	@sql varchar(max)
AS
BEGIN
	declare
		@n int,
		@i int = 0,
		@s int = 0,	-- substring start posotion
		@l int;		-- substring length

	set @n = ceiling(len(@sql) / 8000.0);

	while @i < @n
	begin
		set @l = 8000 - charindex(char(13), reverse(substring(@sql, @s, 8000)));
		print substring(@sql, @s, @l);
		set @i = @i + 1;
		set @s = @s + @l + 2; -- accumulation + CR/LF
	end

	return 0
END

I just created a SP out of Ben's great answer:

/*
---------------------------------------------------------------------------------
PURPOSE   : Print a string without the limitation of 4000 or 8000 characters.
https://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
USAGE     : 
DECLARE @Result NVARCHAR(MAX)
SET @Result = 'TEST'
EXEC [dbo].[Print_Unlimited] @Result
---------------------------------------------------------------------------------
*/
ALTER PROCEDURE [dbo].[Print_Unlimited]
	@String NVARCHAR(MAX)
AS

BEGIN

	BEGIN TRY
	---------------------------------------------------------------------------------

	DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
	DECLARE @Offset TINYINT; /* tracks the amount of offset needed */
	SET @String = replace(replace(@String, CHAR(13) + CHAR(10), CHAR(10)), CHAR(13), CHAR(10))

	WHILE LEN(@String) > 1
	BEGIN
		IF CHARINDEX(CHAR(10), @String) BETWEEN 1 AND 4000
		BEGIN
			SET @CurrentEnd =  CHARINDEX(CHAR(10), @String) -1
			SET @Offset = 2
		END
		ELSE
		BEGIN
			SET @CurrentEnd = 4000
			SET @Offset = 1
		END   
		PRINT SUBSTRING(@String, 1, @CurrentEnd) 
		SET @String = SUBSTRING(@String, @CurrentEnd + @Offset, LEN(@String))   
	END /*End While loop*/

	---------------------------------------------------------------------------------
	END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage VARCHAR(4000)
		SELECT @ErrorMessage = ERROR_MESSAGE()    
		RAISERROR(@ErrorMessage,16,1)
	END CATCH
END

I was looking to use the print statement to debug some dynamic sql as I imagin most of you are using print for simliar reasons.

I tried a few of the solutions listed and found that Kelsey's solution works with minor tweeks (@sql is my @script) n.b. LENGTH isn't a valid function:

--http://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
--Kelsey
DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@sql) / 4000) + 1
WHILE @Counter < @TotalPrints 
BEGIN
	PRINT SUBSTRING(@sql, @Counter * 4000, 4000)
	SET @Counter = @Counter + 1
END
PRINT LEN(@sql)

This code does as commented add a new line into the output, but for debugging this isn't a problem for me.

Ben B's solution is perfect and is the most elegent, although for debugging is a lot of lines of code so I choose to use my slight modification of Kelsey's. It might be worth creating a system like stored procedure in msdb for Ben B's code which could be reused and called in one line?

Alfoks' code doesn't work unfortunately because that would have been easier.

You can use this

declare @i int = 1
while Exists(Select(Substring(@Script,@i,4000))) and (@i < LEN(@Script))
begin
	 print Substring(@Script,@i,4000)
	 set @i = @i+4000
end

Or simply:

PRINT SUBSTRING(@SQL_InsertQuery, 1, 8000)
PRINT SUBSTRING(@SQL_InsertQuery, 8001, 16000)

create procedure dbo.PrintMax @text nvarchar(max)
as
begin
declare @i int, @newline nchar(2), @print varchar(max);
set @newline = nchar(13) + nchar(10);
select @i = charindex(@newline, @text);
while (@i > 0)
begin
select @print = substring(@text,0,@i);
while (len(@print) > 8000)
begin
print substring(@print,0,8000);
select @print = substring(@print,8000,len(@print));
end
print @print;
select @text = substring(@text,@i+2,len(@text));
select @i = charindex(@newline, @text);
end
print @text;
end

Uses Line Feeds and spaces as a good break point:

declare @sqlAll as nvarchar(max)
set @sqlAll = '-- Insert all your sql here'

print '@sqlAll - truncated over 4000'
print @sqlAll
print '   '
print '   '
print '   '
    
print '@sqlAll - split into chunks'
declare @i int = 1, @nextspace int = 0, @newline nchar(2)
set @newline = nchar(13) + nchar(10)

    
while Exists(Select(Substring(@sqlAll,@i,3000))) and (@i < LEN(@sqlAll))
begin
    while Substring(@sqlAll,@i+3000+@nextspace,1) <> ' ' and Substring(@sqlAll,@i+3000+@nextspace,1) <> @newline
    BEGIN
        set @nextspace = @nextspace + 1
    end
    print Substring(@sqlAll,@i,3000+@nextspace)
    set @i = @i+3000+@nextspace
    set @nextspace = 0
end
print '   '
print '   '
print '   '

There is great function called PrintMax written by Bennett Dill.

Here is slightly modified version that uses temp stored procedure to avoid "schema polution"(idea from https://github.com/Toolien/sp_GenMerge/blob/master/sp_GenMerge.sql)

EXEC (N'IF EXISTS (SELECT * FROM tempdb.sys.objects 
                   WHERE object_id = OBJECT_ID(N''tempdb..#PrintMax'') 
                   AND type in (N''P'', N''PC''))
	DROP PROCEDURE #PrintMax;');
EXEC (N'CREATE PROCEDURE #PrintMax(@iInput NVARCHAR(MAX))
AS
BEGIN
    IF @iInput IS NULL
    RETURN;

    DECLARE @ReversedData NVARCHAR(MAX)
          , @LineBreakIndex INT
          , @SearchLength INT;

    SET @SearchLength = 4000;
     
    WHILE LEN(@iInput) > @SearchLength
    BEGIN
    SET @ReversedData = LEFT(@iInput COLLATE DATABASE_DEFAULT, @SearchLength);
    SET @ReversedData = REVERSE(@ReversedData COLLATE DATABASE_DEFAULT);
    SET @LineBreakIndex = CHARINDEX(CHAR(10) + CHAR(13),
                          @ReversedData COLLATE DATABASE_DEFAULT);
    PRINT LEFT(@iInput, @SearchLength - @LineBreakIndex + 1);
    SET @iInput = RIGHT(@iInput, LEN(@iInput) - @SearchLength 
                        + @LineBreakIndex - 1);
    END;

    IF LEN(@iInput) > 0
    PRINT @iInput;
END;');

DBFiddle Demo

EDIT:

Using CREATE OR ALTER we could avoid two EXEC calls:

EXEC (N'CREATE OR ALTER PROCEDURE #PrintMax(@iInput NVARCHAR(MAX))
AS
BEGIN
    IF @iInput IS NULL
    RETURN;

    DECLARE @ReversedData NVARCHAR(MAX)
          , @LineBreakIndex INT
          , @SearchLength INT;

    SET @SearchLength = 4000;
     
    WHILE LEN(@iInput) > @SearchLength
    BEGIN
    SET @ReversedData = LEFT(@iInput COLLATE DATABASE_DEFAULT, @SearchLength);
    SET @ReversedData = REVERSE(@ReversedData COLLATE DATABASE_DEFAULT);
    SET @LineBreakIndex = CHARINDEX(CHAR(10) + CHAR(13), @ReversedData COLLATE DATABASE_DEFAULT);
    PRINT LEFT(@iInput, @SearchLength - @LineBreakIndex + 1);
    SET @iInput = RIGHT(@iInput, LEN(@iInput) - @SearchLength + @LineBreakIndex - 1);
    END;

    IF LEN(@iInput) > 0
    PRINT @iInput;
END;');

db<>fiddle Demo

My PrintMax version for prevent bad line breaks on output:

CREATE PROCEDURE [dbo].[PrintMax](@iInput NVARCHAR(MAX))
AS
BEGIN
Declare @i int;
Declare @NEWLINE char(1) = CHAR(13) + CHAR(10);
While LEN(@iInput)>0 BEGIN
Set @i = CHARINDEX(@NEWLINE, @iInput)
if @i>8000 OR @i=0 Set @i=8000
Print SUBSTRING(@iInput, 0, @i)
Set @iInput = SUBSTRING(@iInput, @i+1, LEN(@iInput))
END
END

Here's another version. This one extracts each substring to print from the main string instead of taking reducing the main string by 4000 on each loop (which might create a lot of very long strings under the hood - not sure).

CREATE PROCEDURE [Internal].[LongPrint]
	@msg nvarchar(max)
AS
BEGIN

	-- SET NOCOUNT ON reduces network overhead
	SET NOCOUNT ON;

	DECLARE @MsgLen int;
	DECLARE @CurrLineStartIdx int = 1;
	DECLARE @CurrLineEndIdx int;
	DECLARE @CurrLineLen int;	
	DECLARE @SkipCount int;

	-- Normalise line end characters.
	SET @msg = REPLACE(@msg, char(13) + char(10), char(10));
	SET @msg = REPLACE(@msg, char(13), char(10));

	-- Store length of the normalised string.
	SET @MsgLen = LEN(@msg);		
	
	-- Special case: Empty string.
	IF @MsgLen = 0
	BEGIN
		PRINT '';
		RETURN;
	END
	
	-- Find the end of next substring to print.
	SET @CurrLineEndIdx = CHARINDEX(CHAR(10), @msg);
	IF @CurrLineEndIdx BETWEEN 1 AND 4000
	BEGIN
		SET @CurrLineEndIdx = @CurrLineEndIdx - 1
		SET @SkipCount = 2;
	END
	ELSE
	BEGIN
		SET @CurrLineEndIdx = 4000;
		SET @SkipCount = 1;
	END		

	-- Loop: Print current substring, identify next substring (a do-while pattern is preferable but TSQL doesn't have one).
	WHILE @CurrLineStartIdx < @MsgLen
	BEGIN
		-- Print substring.
		PRINT SUBSTRING(@msg, @CurrLineStartIdx, (@CurrLineEndIdx - @CurrLineStartIdx)+1);
	
		-- Move to start of next substring.
		SET @CurrLineStartIdx = @CurrLineEndIdx + @SkipCount;

		-- Find the end of next substring to print.
		SET @CurrLineEndIdx = CHARINDEX(CHAR(10), @msg, @CurrLineStartIdx);
		SET @CurrLineLen = @CurrLineEndIdx - @CurrLineStartIdx;

		-- Find bounds of next substring to print.				
		IF @CurrLineLen BETWEEN 1 AND 4000
		BEGIN
			SET @CurrLineEndIdx = @CurrLineEndIdx - 1
			SET @SkipCount = 2;
		END
		ELSE
		BEGIN
			SET @CurrLineEndIdx = @CurrLineStartIdx + 4000;
			SET @SkipCount = 1;
		END
	END
END

This should work properly this is just an improvement of previous answers.

DECLARE @Counter INT
DECLARE @Counter1 INT
SET @Counter = 0
SET @Counter1 = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@QUERY) / 4000) + 1
print @TotalPrints 
WHILE @Counter < @TotalPrints 
BEGIN
-- Do your printing...
print(substring(@query,@COUNTER1,@COUNTER1+4000))

set @COUNTER1 = @Counter1+4000
SET @Counter = @Counter + 1
END

If the source code will not have issues with LF to be replaced by CRLF, No debugging is required by following simple codes outputs.

--http://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
--Bill Bai
SET @SQL=replace(@SQL,char(10),char(13)+char(10))
SET @SQL=replace(@SQL,char(13)+char(13)+char(10),char(13)+char(10) )
DECLARE @Position int 
WHILE Len(@SQL)>0 
BEGIN
SET @Position=charindex(char(10),@SQL)
PRINT left(@SQL,@Position-2)
SET @SQL=substring(@SQL,@Position+1,len(@SQL))
end;