title: Dynamic SQL concatenate strings
body: <p>Please see the SQL below:</p>
  
  <pre><code>declare @TableName VarChar(100)
  declare @field1 varchar(100)
  declare @field2 varchar(100)
  set @TableName = 'Person.Person'
  set @field1  = 'LastName'
  set @field2  = 'FirstName'
  Declare @SQL VarChar(1000) SELECT @SQL = 'SELECT ' + @field1 + ',' + @field2 + ' FROM '
   SELECT @SQL = @SQL + @TableName 
   Exec ( @SQL) 
  </code></pre>
  
  <p>The result set appears in two columns i.e. firstname and surname.  Is it possible to concatenate the columns so the output appears like this?</p>
  
  <pre><code>Ian,McFearce
  Jane,McAndrew
  </code></pre>
  
  <p>I have found a quote on this website: <a href="http://technet.microsoft.com/en-us/library/ms188001.aspx" rel="nofollow">http://technet.microsoft.com/en-us/library/ms188001.aspx</a>, which states:  "concatenating two strings with the + operator, are not allowed".  Therefore I suspect it is not possible.  Is there a workaround.</p>