Character Limit for a Variable- Exec(@Variable) issues and solutions

Hi All,

When you are using Varchar (MAX) in declaring a Variable, Always remember, that VARCHAR(MAX) can hold only 8000 characters.

When we try to print Variables, PRINT Command display only 4000 character max.

When we try to execute, Variable (MAX) which is more than 8000 Character, we may not get exact Error to solve.

The problems seems to be with SET Statement which accepts only 4000 Characters.

In Order to Execute a Variable which is having VARCHAR (MAX) and the length of the strings are more than 8000 Character, Then best way to work is Splitting the Queries with Multiple Variable.

Example, If you have Dynamic Query which is 10000 characters, and is used as a string in a variable, Then,
we need to Split the Queries.

Find a Logical Break Point and Then concatenate remaining variables.

DECLARE @Query VARCHAR(MAX)

      DECLARE @Query1 VARCHAR(MAX)

      DECLARE @Query2 VARCHAR(MAX)

      DECLARE @Query3 VARCHAR(MAX)

      SET @Query1 = 'Select ……….[4000 Characters] '

SET @Query2= ' [Remaining next 4000 Characters]

SET @Query3= ' [Remaining next 4000 Characters till END of the Query]

     

      SET @Query= @Query1+@Query2+@Query3+@Query4

      EXEC (@Query)

This will work fine.

When you are working with Nvarchar, Then Instead of EXEC (@Query),
use, Execute Sp_ExecuteSQL @Query; When Using SP_EXECUTESQL function, use SET @Query1 = N'Select.......