SQL Server solves the problem of dynamic characters exceeding 8000

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.