title: Using dynamic SQL to create single 'cell' string. Returning this string via SELECT clause
body: <p>The following should ultimately return a single scalar value that is a string separated list of account numbers for supplier with ID 1179. With the LEFT function, this works as intended. Without the LEFT function, I see only 'Command(s) completed successfully'. 
  I've looked at the QUOTENAME function documentation and it says it is only capable of returning a 128 character string. Is that why I can't see the full list of accounts (easily in excess of 128 characters)? 
  Is there some way to achieve what I want using another method?</p>
  
  <p>Code:</p>
  
  <pre><code>DECLARE @str1 VARCHAR(MAX)
  DECLARE @str2 VARCHAR(MAX)
  
  SET @str1 = LEFT(STUFF(
  (
      SELECT DISTINCT ', ' + cpl.clplAcNo
      FROM tblSuppliers s
      JOIN tblClientPriceLists cpl ON (cpl.clplSupplierId = supRowID)
      WHERE s.supRowID = 1179
          AND clplAcNo IS NOT NULL AND clplAcNo &lt;&gt; ''
      FOR XML PATH('')
  ), 1, 1, ''), 300)
  
  SET @str2 = 'SELECT ' + QUOTENAME(@str1, CHAR(39))
  
  EXEC(@str2)
  </code></pre>