title: SQL Server String Concatenation with Varchar(max)
body: <p>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.</p>
  
  <pre><code>--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 ??????????????????
  </code></pre>