SQL Server String Concatenation with Varchar(max)

Question

I’m trying to produce a list of column names, for use in a dynamic query, by concatenating strings into a varchar max. The method I’ve been using has been working well until I came across a table with over 500 columns. With the large table my column list is being truncated when I add the extra strings to the column to produce a sql select script. If anyone could point out to me why my final 4 selects (below) are not all producing 600 rows (when the field is copied to a new query window, the field contains line breaks) I would very much appreciate it. Varchar(max) should be large enough to produce the result I want. I just can’t figure out why the truncation is occurring.

--CREATE TABLE [dbo].[Fact_NULLs_TEST]
--(
--My_really_quite_quite_quite_long_Column_name1 DECIMAL(18,2) NULL,
--My_really_quite_quite_quite_long_Column_name2 DECIMAL(18,2) NULL,
--My_really_quite_quite_quite_long_Column_name3 DECIMAL(18,2) NULL,
--My_really_quite_quite_quite_long_Column_name4 DECIMAL(18,2) NULL,
--My_really_quite_quite_quite_long_Column_name5 DECIMAL(18,2) NULL,
--My_really_quite_quite_quite_long_Column_name6 DECIMAL(18,2) NULL,
--My_really_quite_quite_quite_long_Column_name7 DECIMAL(18,2) NULL,
--My_really_quite_quite_quite_long_Column_name8 DECIMAL(18,2) NULL,
--My_really_quite_quite_quite_long_Column_name9 DECIMAL(18,2) NULL,
---- ......... etc up to My_really_quite_quite_quite_long_Column_name600
--)

DECLARE @Str AS VARCHAR(MAX)
DECLARE @Str2 AS VARCHAR(MAX)
DECLARE @Str3 AS VARCHAR(MAX)
DECLARE @Str4 AS VARCHAR(MAX)
SET @Str = ''
SET @Str2 = ''
SET @Str3 = ''
SET @Str4 = ''


SELECT 
    @Str = @Str + CAST(QUOTENAME(name) AS VARCHAR(MAX)) +  CAST(CHAR(13) AS VARCHAR(MAX))
FROM
MY_DB.sys.columns 
WHERE [object_id] = OBJECT_ID('[MY_DB].[dbo].Fact_NULLs_TEST')


SELECT 
    @Str2 = CAST(@Str2 AS VARCHAR(MAX)) +  CAST(QUOTENAME(name) AS VARCHAR(MAX)) + CAST('my short text' AS VARCHAR(MAX)) + CAST(CHAR(13) AS VARCHAR(MAX))
FROM
MY_DB.sys.columns 
WHERE [object_id] = OBJECT_ID('[MY_DB].[dbo].Fact_NULLs_TEST')


SELECT 
    @Str3 = CAST(@Str3 AS VARCHAR(MAX)) +  CAST(QUOTENAME(name) AS VARCHAR(MAX)) + CAST(' my long text my long text my long text my long text my long text ' AS VARCHAR(MAX)) + CAST(CHAR(13) AS VARCHAR(MAX))
FROM
MY_DB.sys.columns 
WHERE [object_id] = OBJECT_ID('[MY_DB].[dbo].Fact_NULLs_TEST')



SELECT 
    @Str4 = CAST(@Str4 AS VARCHAR(MAX)) + CAST('SUM(CASE WHEN ' AS VARCHAR(MAX)) +  CAST(QUOTENAME(name) AS VARCHAR(MAX)) +  CAST(' IS NULL THEN 1 ELSE 0 END) AS ' AS VARCHAR(MAX)) +  CAST(QUOTENAME(name) AS VARCHAR(MAX)) +  CAST(',' AS VARCHAR(MAX)) +  CAST(CHAR(13) AS VARCHAR(MAX))
FROM
MY_DB.sys.columns 
WHERE [object_id] = OBJECT_ID('[MY_DB].[dbo].Fact_NULLs_TEST')



SELECT @Str , LEN(@Str) --- 600 rows

SELECT @Str2, LEN(@Str2) --- 600 rows

SELECT @Str3 , LEN(@Str3) --- 378 rows ??????????????????

SELECT @Str4 , LEN(@Str4) --- 303 rows ??????????????????

Answer 1

this happens cause quotename is returning null if the input string exceed its maximum which is 128 chars. More info at:

https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql?view=sql-server-2017

within your code is also not needed to add a cast on every part, since you are concatenating a declared varchar(max) variable, Str you can simply add all the content using the plus , even the brakets, like this:

@Str = @Str + '['+name+']'+ CHAR(13)

Anyway it looks like there is a not documented limit on the max length that is returned from a single selectable object either working with SQLCMD and working with SQL Server Management Studio as client to execute the SQL so rather than having everything in a single string you can obtain the right text by a simple straight select, or in case you are using it inside a program you need to open a cursor and fetch every row from the resultingset individually.

I am including the exact SQL i used to test with annotated my findings.

USE MY_DB

DECLARE @Str AS VARCHAR(MAX)
DECLARE @Str2 AS VARCHAR(MAX)
DECLARE @Str3 AS VARCHAR(MAX)
DECLARE @Str4 AS VARCHAR(MAX)
SET @Str = ''
SET @Str2 = ''
SET @Str3 = ''
SET @Str4 = ''


SELECT  
    @Str = @Str + '[' + name + ']'+ CHAR(13) 
FROM
sys.columns 
WHERE [object_id] = OBJECT_ID('[dbo].Fact_NULLs_TEST')

/*
SELECT 
    @Str2 = CAST(@Str2 AS VARCHAR(MAX)) +  CAST(QUOTENAME(name) AS VARCHAR(MAX)) + CAST('my short text' AS VARCHAR(MAX)) + CAST(CHAR(13) AS VARCHAR(MAX))
FROM
sys.columns 
WHERE [object_id] = OBJECT_ID('[MY_DB].[dbo].Fact_NULLs_TEST')


SELECT 
    @Str3 = CAST(@Str3 AS VARCHAR(MAX)) +  CAST(QUOTENAME(name) AS VARCHAR(MAX)) + CAST(' my long text my long text my long text my long text my long text ' AS VARCHAR(MAX)) + CAST(CHAR(13) AS VARCHAR(MAX))
FROM
sys.columns 
WHERE [object_id] = OBJECT_ID('[MY_DB].[dbo].Fact_NULLs_TEST')


*/
SELECT 
    --top 50 
    @Str4 = @Str4 + 
    ' SUM(CASE WHEN ' +'['+ sc.name +']' + ' IS NULL THEN 1 ELSE 0 END) AS ' +'[' + sc.name +'],' + CHAR(13)
FROM
sys.columns sc
WHERE [object_id] = OBJECT_ID('[dbo].Fact_NULLs_TEST')

SELECT @Str , LEN(@Str) --- 29892 chars
/*
SELECT @Str2, LEN(@Str2) --- 

SELECT @Str3 , LEN(@Str3) ---
*/
SELECT @Str4 , LEN(@Str4) --- 87384 chars BUT in case you use Microsoft SQL Server Management Studio and copy /paste into an editor this is coming out truncated

PRINT @Str4 --This is truncated as well if displayed from Messages tab within Microsoft SQL Server Management Studio or grabbed by using SQLCMD 
--with -o on a text file, i.e. SQLCMD -S dbserver -E -i c:\temp\test1.sql -o c:\temp\test1out.txt

SELECT 
    ' SUM(CASE WHEN ' +'['+ sc.name +']' + ' IS NULL THEN 1 ELSE 0 END) AS ' +'[' + sc.name +'],' 
FROM
sys.columns sc
WHERE [object_id] = OBJECT_ID('[dbo].Fact_NULLs_TEST') 
--This last select works fine either from Microsoft SQL Server Management Studio or by using SQLCMD