How to print VARCHAR(MAX) using Print Statement?

sql sql-server sql-server-2005 tsql sql-server-2008
peter picture peter · Oct 21, 2011 · Viewed 99k times · Source

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

alfoks picture alfoks · May 4, 2012

I know it's an old question, but what I did is not mentioned here.

For me the following worked.

DECLARE @info NVARCHAR(MAX)

--SET @info to something big

PRINT CAST(@info AS NTEXT)

Related questions

Simple DateTime sql query

How do I query DateTime database field within a certain range? I am using SQL SERVER 2005 Error code below SELECT * FROM TABLENAME WHERE DateTime >= 12/04/2011 12:00:00 AM AND DateTime <= 25/05/2011 3:53:04 AM Note that I need to get rows within a certain …

Read More
SET versus SELECT when assigning variables?

What are the differences between the SET and SELECT statements when assigning variables in T-SQL?

Read More
SQL Server Invalid Column name after adding new column

I just added an identity column to an existing table with data through the SSMS Designer, the table updates fine and I can run a select query against it successfully, but after the query finishes I noticed that the new …

Read More