title: Invalid object name #temp in Dynamic SQL and Array of field names
body: <p>I am using #temp table in two separate stored procedures. In one case, it is working ok while in other case it gives error ‘Invalid object name #temp’.</p>
  
  <p>First case:</p>
  
  <pre><code>SELECT SubsID,
         SubsName,
         AbbrName
  INTO   #TEMP
  FROM   SubsList
  WHERE  CAST (SubsID AS VARCHAR(10)) LIKE '%' + @intRight + '%'
          OR SubsName LIKE '%' + @intRight + '%'
  </code></pre>
  
  <p> 
  It is working ok. </p>
  
  <p>In order to make this SQL more dynamic, I am using QUOTENAME and made following changes:
  1.I made datatype of @ColName, @sourceName, @intField and @txtField as sysname.
  2.In fact, I removed param @tableName as sysname (value: #temp was passed to it) as it gave error in Select and Drop SQL.
  3.I am passing one field name in @ColName. It gave error when I passed all three field names.
  4.I declare @cmd nvarchar(max) and assigned SQL to it for execution.  </p>
  
  <pre><code>SET @cmd = N'Select ' + QUOTENAME(@ColName) + 
             N' INTO #temp from ' + QUOTENAME(@sourceName) + 
             N' where CAST(' + QUOTENAME(@intField) + N' AS VARCHAR(10)) like ''%' + @strVal + 
             N'%'' or ' + QUOTENAME(@txtField) + ' like ''%' + @strVal + N'%''' --working
  EXEC sp_executesql @cmd;
  
  SELECT *
  FROM   #temp;
  
  DROP TABLE #temp;
  </code></pre>
  
  <p>I changed EXEC sp_executesql @cmd to EXEC(@cmd) but error remained.</p>
  
  <p>I get error of invalid object name, but if I change #temp to ##temp, this error is not there.</p>
  
  <p>My first question: What could be reason of this error in case of using #temp?
  Second question: How I can make array of field names and pass it to stored procedure?</p>