In addition, EXEC () can also break through this restriction, as mentioned in inside SQL server t-programming:
Concatenating Variables
In SQL Server 2000, EXEC had an advantage over sp_executesql in terms of supporting longer input code. even though technically sp_executesql's input code string was of an NTEXT datatype, you typically wanted to construct the code string in a local variable.
However, you couldn't declare a local variable with a large object type, so practically, query strings executed with sp_executesql were limited to the largest supported length of a Unicode character string (NVARCHAR ), which was 4,000 characters. EXEC, on
Other hand, supported a regular character (VARCHAR) input code string, allowing up to 8000 characters. furthermore, EXEC supports a special functionality that allows you to concatenate multiple variables within the parentheses, each up to the maximum supported
Size of 8000 characters. For example, the following code constructs and invokes a code string that is longer than 8000 characters by concatenating three variables, generating an output with 7999 A characters and one B character:
DECLARE @ sql1 as varchar (8000), @ sql2 as varchar (8000), @ sql3 as varchar (8000 );
SET @ sql1 = 'print ''';
SET @ sql2 = REPLICATE ('A', 7999) + 'B ';
SET @ sql3 = ''';';
EXEC (@ sql1 + @ sql2 + @ sql3 );
However, this technique is very awkward, and it requires some sort batics to construct code strings that are longer than 8000 characters. in SQL Server 2005, this technique is no longer necessary because you can provide the EXEC command with a variable defined
As VARCHAR (MAX) or NVARCHAR (MAX) as input. now the input string can be up to 2 GB in size, meaning over two billion characters in a VARCHAR (MAX) value, and one billion characters in NVARCHAR (MAX ). for example, the following code generates a code string
10,000 PRINT statements within it, producing a printout of all numbers in the range 1 through 10,000, each in a separate output line:
DECLARE @ SQL as varchar (MAX), @ I AS INT;
SET @ SQL = '';
SET @ I = 1;
WHILE I <= 10000
BEGIN
SET @ SQL = @ SQL + 'print '+ CAST (@ I AS VARCHAR (10 ))
+ CHAR (13) + CHAR (10 );
SET @ I = @ I + 1;
END
EXEC (@ SQL );
-------------------- Take notes as a record. Please try again next time.