ansaurus

Question

Answer 1

A:

DECLARE @query VARCHAR(MAX)
DECLARE @query2 VARCHAR(MAX)

-- Do wahtever

EXEC (@query + @query2)

EDIT:

Martin Smith is quite right. It is possible that your query cuts in print. One of the reasons of this cut is NULL value in variable or in column which concatenates with your query and make rest of the query NULL.

Muhammad Kashif Nadeem 2010-09-02 11:25:43

Answer 2

+1 A:

varchar(max) is up to about 2GB are you sure it cuts the ending off or is it just when you print it it only displays the first few hundred characters?

To View long text in SSMS without it getting truncated you can use this trick

SELECT @dynsql AS [processing-instruction(x)] FOR XML PATH('')

Martin Smith 2010-09-02 11:29:26

+1, good trick to see long strings. `max` indicates that the maximum storage size is 2^31-1 bytes, so try `PRINT LEN(@SQLVariable)`, is the value 2,147,483,647? if so then it is the max len and truncation is possible, if not it is a display issue.

KM 2010-09-02 12:28:35

try it out: `declare @x varchar(max); SET @x=REPLICATE('1',1000) +REPLICATE('2',1000) +REPLICATE('3',1000) +REPLICATE('4',1000) +REPLICATE('5',1000) +REPLICATE('6',1000) +REPLICATE('7',1000); SET @x=@x+REPLICATE('8',1000) +REPLICATE('9',1000) +REPLICATE('0',1000); print 'len()='+CONVERT(varchar(10),LEN(@x)); print @x; print RIGHT(@x,2000); SELECT @x AS [processing-instruction(x)] FOR XML PATH('')`

KM 2010-09-02 12:40:18

Thanks Martin, that allows me to see everything :)

mailman1979 2010-09-02 13:25:58

ansaurus

tags:

views:

answers:

Dynamic Declare statements SQL Server

related questions