peter peter - 17 days ago 6
SQL Question

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?

Answer

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 = (LENGTH(@script) / 8000) + 1
WHILE @Counter < @TotalPrints 
BEGIN
    -- Do your printing...
    SET @Counter = @Counter + 1
END