title: Dynamic SQL in stored procedure doesn't return result set; when run in SSMS I get results
body: <p>I have a stored procedure that builds a dynamic sql and executes it. It gives no error messages, but it doesn't not return results. However, when I run the code in the sp manually I get the results I'm looking for. Why would this be?  The sp takes one nvarchar(max) parameter, @CLM. ANSI_NULLS, QUOTED_IDENTIFIER and NOCOUNT are ON. In the sp there is one temp table #tmpAffPeople. Its columns are EmpLogin nvarchar(8), Matter nvarchar(15), NameandTitle nvarchar(max), TotalHours decimal(6,2), ClientMatter varchar(max). The text of the sp is as follows:</p>
  
  <pre><code>DECLARE @SQLString AS nvarchar(max)
  SET @SQLString = ';WITH Emps (EmpLogin, AttorneyAndTitle)
  AS
  (SELECT EmpLogin, 
  CASE WHEN EmpTermDate IS NULL OR EmpTermDate = ' + '''' + '''' + ' THEN LastName + ' + '''' + ', ' + '''' + ' + FirstName + ' + ''''+ ' [' + '''' + ' + Title + ' + '''' + '] ' + '''' +
  'ELSE LastName + ' + '''' + ', ' + '''' + ' + FirstName + ' + '''' + ' [' + '''' + ' + Title +' + '''' +'] (inactive) ' + '''' + ' END as FullName 
  FROM [LitigationHold].[dbo].[Employees] 
  where JobCode IN (' + '''' + '100' + '''' + ',' + '''' + '300' + '''' + ',' + '''' + '400' + '''' + ',' + '''' + '500' + '''' + ',' + '''' + '700' + '''' + ',' + '''' + '800' + '''' + ',' + '''' + '1801' + '''' + '))
  INSERT INTO #tmpAffPeople
  SELECT t.[EmpLogin],t.Matter, e.AttorneyAndTitle, SUM([Workhours]) AS TotalHours,
  c.ClientName + ' + '''' + '/' + '''' + ' + em.MatterDesc
  FROM TimeCard t
  INNER JOIN Emps e
  ON e.EmpLogin = t.EmpLogin
  INNER JOIN EliteMatter em
  ON t.Matter = em.MatterName
  INNER JOIN EliteClient c
  ON c.ClientNum = em.ClientNum
  WHERE t.Matter IN (' + @CLM + ')
  GROUP BY t.EmpLogin, c.ClientName, em.MatterDesc, t.Matter, e.AttorneyAndTitle
  ORDER BY TotalHours'
  </code></pre>
  
  <p>After this runs, in the sp I execute the @SQLString by calling execute with @SQLString right after it between parentheses. I then drop the table. I've done this kind of thing before and never had a problem. What am I missing? I'm using SQL Server 2008R2. </p>