I am trying to concatenates strings in T-SQL like
'This is a test ' + CHAR(13) + CHAR(10) + ' string'
When the string is long (I do not know the limit), I get an error:
Some part of your SQL statement is nested too deeply. Rewrite the
query or break it up into smaller queries.
How can I work around this? How is the SQL statement "nested"?
Should I switch to 0x???? notation if the string length is exceeding some value?
This question was asked 10 years earlier on
without getting a satisfactory answer.
edit
-
The total string to insert (with the concatenations and ':s) is 369711 chars long.
-
There are 6301 instances of
char(13)
and the same ofchar(10)
. -
There are 19050 instances of
+
, not all necessarily used as concatenation operator.
Best Answer
It might depend on what is meant by "too long":
Length: If you mean strings of many characters, then try converting some of them to a
MAX
type (i.e.NVARCHAR(MAX)
orVARCHAR(MAX)
). For example:Concatenations: If you mean operations involving many concatenations, there is definitely a maximum number that can be done in a single statement. I tested this a while ago and was going to blog about it but got distracted ;-). I just reviewed my testing and while there are a few different ways to concatenate, the simplest way (which is what you are doing) has a limit of around 3300 per statement. I say "around" 3300 because it varies based on some other factors related to memory. Sometimes I can get 3310, then I tried 3311 and it works, try again and it fails, try again with 3310 and it fails, try again with 3309 and it works.
If you have over this amount then you have no choice but to split the operation into multiple steps (i.e. multiple
SET
/SELECT
statements). Although, the error for reaching the max here is:And you aren't getting exactly that, so not entirely sure yet until you do more testing, or at least provide the error number. In either case, you can reduce the number of concatenations by combining the "\r" and "\n" into a single variable or
VARBINARY
literal. The following example shows that both methods produce the same output:The benefit of the variable is that it is shorter, which helps keep large scripts a little more readable and not as large. But, you can't highlight one or more random lines and execute, unless you also highlight the
DECLARE
but that might not always work. The benefit of theVARBINARY
literal is that you can easily execute random groupings of statements, or move statements to other scripts / section of this script and not worry about forgetting theDECLARE
. In either case, this might reduce a few hundred of your concatenations (assuming you already have around 3300 of them), but it doesn't prevent the error if you get a string value that requires hundreds more concatenations.It's also quite possible that there's a different and/or simpler approach to what you are ultimately trying to accomplish that doesn't rely on concatenations. So it would help if you could please update the question to specify why you are doing this in the first place :-).
Given three concatenations per set of "CR" + "LF" (i.e.
+ CHAR(13)
is one,+ CHAR(10)
is two, and+ '...'
is the third), that is 18,903 concatenations right there. Yep, that's a bit over 3300 ;-).Ok. But, why? What do you actually gain from this? Embedded CRLF's are just fine for inserting.
Also:
VARCHAR
orNVARCHAR
? In the example in the question, it is onlyVARCHAR
, yet in a comment on the question, you give an example of converting aVARBINARY
literal intoNVARCHAR
.In either case, doing either of the two approaches I noted above to reduce concatenations by one of the two pieces per CRLF won't help here as that will still leave you with 12,602 concatenations. So, you have two options:
Use a replacement / escape sequence In a comment on the question, Martin Smith suggested using
{crlf}
. My preference would be one of the following:CHAR(31)
: This is the non-printable "Unit Separator" character. It's only a single byte, quite unlikely to be in your string data, and won't get converted to something else if you accidentally change encondings. Unfortunately, it's not very readable as it won't even show up as a space, hence the words it separates will appear to have nothing between them.'\n'
: This is the almost universal "newline" escape sequence. This is two bytes and definitely more readable.In either case, you just
INSERT INTO Schema.Table (Column) VALUES (REPLACE(....));
Convert entire string to
VARBINARY
: this results in a very portable, single-line value. This isn't readable, but there won't be any unexpected character conversions either, and you can use a standard ANSI / code page 1252 file encoding, whereas you would need to use either UTF-8 (with signature / BOM) or Unicode / UTF-16 (with signature / BOM) for the file encoding if the string data is Unicode /NVARCHAR
.You mentioned in a comment on the question something about having a byte array. If the source of the data is .NET, then the strings data is already UTF-16 LE (i.e.
NVARCHAR
) so simply doingINSERT INTO Schema.Table (Column) VALUES (0x{hex_from_byte[]});
will work just fine. If the destination is aVARCHAR
column, then you can either:Encoding.GetEncoding(1252).GetBytes(_StringVariable)
INSERT INTO Schema.Table (Column) VALUES (CONVERT(NVARCHAR(MAX), 0x{hex_from_byte[]}));
. Please note that we are converting toNVARCHAR
because the hex representation is UTF-16 LE. The return value from theCONVERT
function will then implicitly convert toVARCHAR
as it is inserted into the column.