[sql] SQL NVARCHAR and VARCHAR Limits

All, I have a large (unavoidable) dynamic SQL query. Due to the number of fields in the selection criteria the string containing the dynamic SQL is growing over 4000 chars. Now, I understand that there is a 4000 max set for NVARCHAR(MAX), but looking at the executed SQL in Server Profiler for the statement

DELARE @SQL NVARCHAR(MAX);
SET @SQL = 'SomeMassiveString > 4000 chars...';
EXEC(@SQL);
GO

Seems to work(!?), for another query that is also large it throws an error which is associated with this 4000 limit(!?), it basically trims all of the SQL after this 4000 limit and leaves me with a syntax error. Despite this in the profiler, it is showing this dynamic SQL query in full(!?).

What exactly is happening here and should I just be converting this @SQL variable to VARCHAR and get on with it?

Thanks for your time.

Ps. It would also be nice to be able to print out more than 4000 chars to look at these big queries. The following are limited to 4000

SELECT CONVERT(XML, @SQL);
PRINT(@SQL);

is there any other cool way?

Okay, so if later on down the line the issue is that you have a query that's greater than the allowable size (which may happen if it keeps growing) you're going to have to break it into chunks and execute the string values. So, let's say you have a stored procedure like the following:

CREATE PROCEDURE ExecuteMyHugeQuery
    @SQL VARCHAR(MAX) -- 2GB size limit as stated by Martin Smith
AS
BEGIN
    -- Now, if the length is greater than some arbitrary value
    -- Let's say 2000 for this example
    -- Let's chunk it
    -- Let's also assume we won't allow anything larger than 8000 total
    DECLARE @len INT
    SELECT @len = LEN(@SQL)

    IF (@len > 8000)
    BEGIN
        RAISERROR ('The query cannot be larger than 8000 characters total.',
                   16,
                   1);
    END

    -- Let's declare our possible chunks
    DECLARE @Chunk1 VARCHAR(2000),
            @Chunk2 VARCHAR(2000),
            @Chunk3 VARCHAR(2000),
            @Chunk4 VARCHAR(2000)

    SELECT @Chunk1 = '',
           @Chunk2 = '',
           @Chunk3 = '',
           @Chunk4 = ''

    IF (@len > 2000)
    BEGIN
        -- Let's set the right chunks
        -- We already know we need two chunks so let's set the first
        SELECT @Chunk1 = SUBSTRING(@SQL, 1, 2000)

        -- Let's see if we need three chunks
        IF (@len > 4000)
        BEGIN
            SELECT @Chunk2 = SUBSTRING(@SQL, 2001, 2000)

            -- Let's see if we need four chunks
            IF (@len > 6000)
            BEGIN
                SELECT @Chunk3 = SUBSTRING(@SQL, 4001, 2000)
                SELECT @Chunk4 = SUBSTRING(@SQL, 6001, (@len - 6001))
            END
              ELSE
            BEGIN
                SELECT @Chunk3 = SUBSTRING(@SQL, 4001, (@len - 4001))
            END
        END
          ELSE
        BEGIN
            SELECT @Chunk2 = SUBSTRING(@SQL, 2001, (@len - 2001))
        END
    END

    -- Alright, now that we've broken it down, let's execute it
    EXEC (@Chunk1 + @Chunk2 + @Chunk3 + @Chunk4)
END

declare @p varbinary(max)
set @p = 0x
declare @local table (col text)

SELECT   @p = @p + 0x3B + CONVERT(varbinary(100), Email)
 FROM tbCarsList
 where email <> ''
 group by email
 order by email

 set @p = substring(@p, 2, 100000)

 insert @local values(cast(@p as varchar(max)))
 select DATALENGTH(col) as collen, col from @local

result collen > 8000, length col value is more than 8000 chars

You mus use nvarchar text too. that's mean you have to simply had a "N" before your massive string and that's it! no limitation anymore

DELARE @SQL NVARCHAR(MAX);
SET @SQL = N'SomeMassiveString > 4000 chars...';
EXEC(@SQL);
GO

The accepted answer helped me but I got tripped up while doing concatenation of varchars involving case statements. I know the OP's question does not involve case statements but I thought this would be helpful to post here for others like me who ended up here while struggling to build long dynamic SQL statements involving case statements.

When using case statements with string concatenation the rules mentioned in the accepted answer apply to each section of the case statement independently.

declare @l_sql varchar(max) = ''

set @l_sql = @l_sql +
case when 1=1 then
    --without this correction the result is truncated
    --CONVERT(VARCHAR(MAX), '')
 +REPLICATE('1', 8000)
 +REPLICATE('1', 8000)
end

print len(@l_sql)