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
Comments