/*
-- =============================================
-- Author : Kawser Sheikh
-- Create date: 04-25-2017
-- Description: IF are trying to print a very large text, then you might encounter TRUNCATION of text in the output window,
So, in that case this StoredProcedure might be useful to print long text
-- =============================================
*/
SET NOCOUNT ON
IF OBJECT_ID('dbo.usp_PrintBig') IS NOT NULL
DROP PROCEDURE dbo.usp_PrintBig
GO
--EXEC usp_PrintBig @dynamicSQL
CREATE PROCEDURE dbo.usp_PrintBig (
@text NVARCHAR(MAX)
)
AS
--DECLARE @text NVARCHAR(MAX) = 'YourTextHere'
DECLARE @lineSep NVARCHAR(2) = CHAR(13) + CHAR(10) -- Windows \r\n
DECLARE @off INT = 1
DECLARE @maxLen INT = 4000
DECLARE @len INT
WHILE @off < LEN(@text)
BEGIN
SELECT @len =
CASE
WHEN LEN(@text) - @off - 1 <= @maxLen THEN LEN(@text)
ELSE @maxLen
- CHARINDEX(REVERSE(@lineSep), REVERSE(SUBSTRING(@text, @off, @maxLen)))
- LEN(@lineSep)
+ 1
END
PRINT SUBSTRING(@text, @off, @len)
--PRINT '@off=' + CAST(@off AS VARCHAR) + ' @len=' + CAST(@len AS VARCHAR)
SET @off += @len + LEN(@lineSep)
END
GO