Customized PRINT Procedure in SQL Server

Hello everyone. In this article, I will try to give information about the customized PRINT procedure in SQL Server.

In SQL Server, the PRINT statement prints up to 4000 characters. So what will you do if the content of the dynamic queries you write is longer than 4000 characters?

In SQL Server, in some cases, you may want to PRINT statements with more than 4000 characters.

You can easily do this using the procedure below.

CREATE PROCEDURE LONGPRINT
    @String NVARCHAR(MAX)
AS
BEGIN
    BEGIN TRY

    DECLARE @CurrentEnd BIGINT;
    DECLARE @Offset TINYINT; 
    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 TRY
    BEGIN CATCH
        DECLARE @ErrorMessage VARCHAR(4000)
        SELECT @ErrorMessage = ERROR_MESSAGE()    
        RAISERROR(@ErrorMessage,16,1)
    END CATCH
END

--Use of the Procedure

DECLARE @Degisken VARCHAR(MAX)
SET @Degisken = REPLICATE('x', 6000)
EXEC LONGPRINT @Degisken

When you create the above procedure and run the code, you will see a result similar to the one below.

As you can see, we have printed 6000 characters with the LONGPRINT procedure.

Good luck to everyone in business and life.