How to concatenate long nvarchar(max) rows to build a dynamic T-SQL

Question

I have a table in SQL Server 2012 BI:

CREATE TABLE CodeParts (
    ID int identity(1,1) not null
    ,Line nvarchar(max) not null
)

loaded with parts of the very long T-SQL query stored in [Line] column. Example:

ID | Line
----------------------
 1 | BEGIN TRAN MERGE someTableWithLotOfColumns dst USING (SELECT...
 2 | WHEN MATCHED THEN CASE WHEN dst.someColumn != src.someColumn...
 3 | WHEN NOT MATCHED...
 4 | OUTPUT...
 5 | ;MERGE... next table with lot of columns blah blah blah
...| ...
25 | ;MERGE... yet another table with lot of columns
60 | COMMIT

The code have 60 lines, each of the line may be up to 12,000 characters because of number of the columns and their names' length.

I need to execute entire code built by all those rows and I don't know how to do that avoiding truncation.

score 1 · Accepted Answer · edited May 23 '17 at 11:54

It can be very tricky to work with longer string. Check this:

DECLARE @txt NVARCHAR(MAX)=(SELECT REPLICATE('x',12000));
SELECT LEN(@txt) AS CountCharacters
      ,DATALENGTH(@txt) AS UsedBytes;

Although one might think this is declared as NVARCHAR(MAX) the given 'x' isn't. This let's the string be a normal string with a smaller size limit. Now try this (only difference is the CAST('x' AS NVARCHAR(MAX))):

DECLARE @txt2 NVARCHAR(MAX)=(SELECT REPLICATE(CAST('x' AS NVARCHAR(MAX)),12000));
SELECT LEN(@txt2) AS CountCharacters
      ,DATALENGTH(@txt2) AS UsedBytes;

To demonstrate this I create a working example with a dummy table with 60 row each row consisting of 12.000 characters.

DECLARE @tbl TABLE(ID INT IDENTITY,CodeLine NVARCHAR(MAX));

WITH TallySixty AS (SELECT TOP 60 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Dummy FROM master..spt_values)
INSERT INTO @tbl
SELECT REPLICATE(CAST(RIGHT(Dummy,1) AS NVARCHAR(MAX)),12000)
FROM TallySixty;

SELECT CodeLine,LEN(CodeLine) AS CountCharacters
      ,DATALENGTH(CodeLine) AS UsedBytes FROM @tbl

DECLARE @concatString NVARCHAR(MAX)=
(
    SELECT(SELECT CodeLine + ' '  FROM @tbl FOR XML PATH(''),TYPE).value('(text())[1]','nvarchar(max)')
);

SELECT @concatString
      ,LEN(@concatString) AS CountCharacters
      ,DATALENGTH(@concatString) AS UsedBytes

The final result shows clearly, that the resulting string has the length of 60 times 12.000 (plus the added blanks) and is twice this sice in memory due to NVARCHAR. Up to ~2GB this concatenation should work. According to this this is pretty much enough :-)

I think, that EXEC is able to deal with NVARCHAR(MAX) up to the full size.

`nvarchar(max)` *variables* aren't [restricted to that pesky 2GB limit](http://stackoverflow.com/q/7611394/15498) — Damien_The_Unbeliever, Mar 17 '17 at 07:41
@Damien_The_Unbeliever Thx! I just tested my code from above (some `CROSS JOINs` in the *Tally*) using `TOP 22000` and `REPLICATE` with 100.000, which should result slightly above the *magic limit*. I got - after almost 4 minutes - the exception 6365: (own translation) *An XML-process resulted in a XML data type bigger than 2GB. The process has terminated*. — Shnugo, Mar 17 '17 at 08:25
yes, so that would appear to be a limitation of (ab-)using the XML system to perform concatenation. — Damien_The_Unbeliever, Mar 17 '17 at 08:27
Works! The last query shows CountCharacters=720059 and UsedBytes=1440120. Thank you. — derlee, Mar 20 '17 at 16:46

Nolan Shang · Answer 2 · 2017-03-17T13:52:57.133

0

DECLARE @sql NVARCHAR(max)
SET @sql=ISNULL(@sql+CHAR(13),'')+Line FROM CodeParts order by id
EXEC(@SQL)

The @sql variable must be declared with MAX as length. If the string is more than 4000, you may cannot print whole string, but it can be executed.

edited Mar 17 '17 at 13:52

answered Mar 17 '17 at 13:26

Nolan Shang

2,312
1
14
10

1) Not guaranteed to work, and 2) Definitely not guaranteed to be retrieving rows in the correct order, absent an `ORDER BY`. But pay attention to (1) again - and [this page](https://msdn.microsoft.com/en-GB/library/ms189484.aspx#Remarks): "Do not use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur. This is because all expressions in the SELECT list (including assignments) are not guaranteed to be executed exactly once for each output row" – Damien_The_Unbeliever Mar 17 '17 at 13:29

How to concatenate long nvarchar(max) rows to build a dynamic T-SQL

2 Answers2