Hello Madhivanan,

In this case you need to print the statements individually,

DECLARE @SQL1 VARCHAR(800),@SQL2 VARCHAR(800);

SET @SQL1 = ' SOME QUERY WITH 8000 LENGTH'; SET @SQL2 = ' SOME QUERY WITH 8000 LENGTH';

EXEC (@SQL1 + @SQL2) ; /This will work */ PRINT (@SQL1 + @SQL2); / This will not print all the query */

/* /* This will print whole query*/ PRINT (@SQL1); PRINT (@SQL2) ; */

A workaround that I use is to do the following. (make sure that you have set your SSMS window to send results to GRID)

SELECT @sql as 'processing-instruction(x)'
FOR XML PATH(''),TYPE

This will return an XML data type value. Click on the result grid cell and SSMS will open a new query window with the content of the variable. That too, without losing the formatting!

Thanks Jacob, I am using your query to check generated query without losing the formatting. I learned from you only.

This command is print / display only 8000 characters. Code sample for the same is as below:

declare @p varchar(max)

set @p = REPLICATE('a',3000) + CHAR(10) + REPLICATE('b',3000) + CHAR(10)+ REPLICATE('c',1996) + CHAR(10)
+ REPLICATE('d',3000) + CHAR(10)

print @p

SELECT @p as 'processing-instruction(x)'
FOR XML PATH(''),TYPE

Both the command has similar output. Am I missing anything?

Hardik,

Unfortunately, the length of your string is just 8000 only. Try the following

DECLARE @p VARCHAR(MAX)
SELECT @p = REPLICATE('a', 5000) 
SELECT LEN(@p)
-- RETURNS 5000

SELECT @p =  REPLICATE('a', 5000) + REPLICATE('b', 5000)
SELECT LEN(@p)
 -- You would expect to see 10,000
 -- But result is 8000

The reason for this behaviour is that, each of the REPLICATE() function above returns a VARCHAR(8000) value. The result of two operations involving VARCHAR(8000) data type will only be VARCHAR(8000).

To get the desired result, you should force the REPLICATE() function to return a VARCHAR(MAX) value. This can be done as shown in the example below.

SELECT @p =  REPLICATE(CAST('a' AS VARCHAR(MAX)), 5000) + REPLICATE('b', 5000)
SELECT LEN(@p)
-- RETURNS 10,000

In the above example, the first REPLICATE() function returns a VARCHAR(MAX) value. An operation that involves a VARCHAR(MAX) and VARCHAR(8000) values will result in VARCHAR(MAX) output.

You might have also received the same result if you attempted the original example in two steps. See this example:

DECLARE @p VARCHAR(MAX)
SELECT @p = REPLICATE('a', 5000) 
SELECT @p = @p + REPLICATE('b', 5000) 
SELECT LEN(@p)
-- RETURNS 10,000

Interesting, isn't it? :-)

Thanks Jacob. Really interesting.

Now following code returns the result properly:

DECLARE @p VARCHAR(MAX)
SELECT  @p = REPLICATE(CAST('a' AS VARCHAR(MAX)), 5000) + CHAR(10)
        + REPLICATE('b', 3000) + CHAR(10) + REPLICATE('d', 3000)

SELECT  @p AS 'processing-instruction(x)'
FOR     XML PATH(''),
            TYPE