SSMS default maximum number of characters displayed in each column is 256. You can change this option on: Option/Query Results/SQL Server/Results to Text but, the maximum value is 8192 characters.

This is a solution when you need to print more that 8192 characters:

 1use master;
 2go
 3drop proc  if exists sp_Print;
 4go
 5create or alter proc sp_Print
 6(  @Text nvarchar(max) )
 7 as
 8begin
 9	declare @pText nvarchar(max) = @Text;
10	declare @pTextNewLine nvarchar(2) = CHAR(13) + CHAR(10); -- ** it is a good practice to use CR and LF together. CHAR(13) + CHAR(10)
11	declare @pTextMax int = 200;  -- ** default maximum number caracters displayed - SSMS -- but you can change it
12	declare @pTextPos int = 1;
13	declare @pTextCR Int;
14	declare @pTextLen Int;
15	select @pTextLen = LEN(@pText);
16	while (@pTextPos <  @pTextLen)
17	begin
18		 SELECT @pTextCR = CHARINDEX(@pTextNewLine, @pText, @pTextPos);
19		 if (@pTextCR-@pTextPos > @pTextMax or @pTextCR = 0) 
20		 begin
21			PRINT SUBSTRING(@pText, @pTextPos, @pTextMax);
22			 SET @pTextPos = @pTextPos+@pTextMax
23		 end
24		 else
25		 begin
26			PRINT SUBSTRING(@pText, @pTextPos, @pTextCR-@pTextPos);
27			 SET @pTextPos = @pTextPos+@pTextCR-@pTextPos
28		 end
29	end
30end
31go

This is a solution when you need to print a XML:

 1
 2use master
 3go
 4drop proc  if exists sp_PrintXml;
 5go
 6create or alter proc sp_PrintXml
 7( @Xml Xml )
 8 as
 9begin
10	declare @pText nvarchar(max) = cast(@Xml as nvarchar(max));
11	declare @pTextNewLine nvarchar(2) = '><'
12	declare @pTextMax int = 1000;  
13	declare @pTextPos int = 1;
14	declare @pTextCR Int;
15	declare @pTextLen Int;
16	declare @pTextTab int = 0;
17	declare @pTextPrint nvarchar(max)
18	select @pTextLen = LEN(@pText);
19	while (@pTextPos <  @pTextLen)
20	begin
21
22		 SELECT @pTextCR = CHARINDEX(@pTextNewLine, @pText, @pTextPos);
23
24		 if (@pTextCR-@pTextPos > @pTextMax or @pTextCR = 0) 
25		 begin
26			set @pTextPrint = SUBSTRING(@pText, @pTextPos, @pTextMax+1);
27			SET @pTextPos = @pTextPos+@pTextMax+1
28		 end
29		 else
30		 begin
31			set @pTextPrint = SUBSTRING(@pText, @pTextPos, @pTextCR-@pTextPos+1);
32			SET @pTextPos = @pTextPos+@pTextCR-@pTextPos+1
33		 end
34
35		 if (CHARINDEX('</',  LTRIM(@pTextPrint) ) = 0) begin PRINT space(@pTextTab*2) +  @pTextPrint; set @pTextTab = @pTextTab+1; end else
36		 if (CHARINDEX('</',  LTRIM(@pTextPrint) ) = 1) begin set @pTextTab = @pTextTab-1; PRINT space(@pTextTab*2) +  @pTextPrint; end else
37		 PRINT space(@pTextTab*2) +  @pTextPrint;
38
39	end
40end
41go
42EXEC sp_ms_marksystemobject 'sp_PrintXml';
43go

Using the NOWAIT option with the SQL Server RAISERROR statement

1
2 RAISERROR (@TextPrint, 0, 1) WITH NOWAIT

The steps to create a stored procedure that is accessible to all databases on the Server, as well as be able to run under the context of the current database when it is called:

  • Master Database - The stored procedure should be created in the Master database
  • Prefix Stored Procedure - The stored procedure name should be prefixed with sp_
  • Mark SP as System Object - Call sp_ms_marksystemobject to mark custom SP as a system object

source: https://www.mssqltips.com/sqlservertip/1660/using-the-nowait-option-with-the-sql-server-raiserror-statement/ https://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement