title: Using Temp table in SQL SP - Not returning Values
body: <p>i Guys need your help again before I go insane...</p>
  
  <p>I have the following SP now if I execute it within the SP it works fine, but as soon as I execute the sp I get no results....</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[BIDASHBOARD_MiningUtilization] 
  
   @DST datetime, @DET datetime, @Equipment int
  
  AS
  BEGIN
  
  --DECLARE @DST datetime, @DET datetime, @Equipment int
  --SET @DET = @DET
  --SET @DET = '2014-02-01 00:00:00.000'
  --SET @DST = DATEADD(dd,1,@DST)
  --SET @DST = '2014-02-28 00:00:00.000'
  --SET @Equipment = 8
  
  
  DROP TABLE #TMP1
  CREATE TABLE #TMP1 (DownTime int, DType varchar(50));
  INSERT INTO #TMP1
  
  SELECT SUM(UTILSUM.[Minutes])/ 60 AS DOWNTIME, UTILSUM.DTYPE
  
  FROM
  
  (SELECT TOTAL.EquipmentID, TOTAL.DownTimeReasonCategoryID, TOTAL.ReasonNote,TOTAL.SDATE,TOTAL.EDATE, DATEDIFF(Mi,TOTAL.SDATE,TOTAL.EDATE) AS [Minutes],TOTAL.DTYPE
  FROM
  
  (SELECT 
        [EquipmentID]
        ,[DownStartTime]
        ,[DownEndTime]
        ,[DownTimeReasonCategoryID]
        ,[ReasonNote]
  
        ,SDATE = 
        CASE 
        WHEN [DownStartTime] BETWEEN @DET AND @DST THEN [DownStartTime]
          ELSE @DET
          END
          ,EDATE =
          CASE 
        WHEN [DownEndTime] BETWEEN @DET AND @DST THEN [DownEndTime]
          ELSE @DST 
          END
          , DTYPE =
          CASE
          WHEN [DownTimeReasonCategoryID] IN (2,5,7,9,11)  THEN 'Equipment Unavailable'
          WHEN [DownTimeReasonCategoryID] IN (1,3,8,12,13)  THEN'Equipment Not Utilized'
          ELSE 'CAT6'
          END
  
    FROM RPZCBIDashboard].[dbo].[Mining_DOWNTIMETEST]
   Where DownStartTime &lt; @DST 
  AND DownEndTime &gt;= @DET
    and EquipmentID in (SELECT[EquipmentId]
  
    FROM RPZCBIDashboard].[dbo].[Mining_Equipment]
    Where EquipmentTypeId = @Equipment
    and IsActive = 1
   and EquipmentDownTime = 1)) AS TOTAL) AS UTILSUM
   GROUP BY UTILSUM.DTYPE
  
    SELECT  #TMP1.DownTime, #TMP1.DType  FROM #TMP1 WHERE #TMP1.DType  &lt;&gt; 'CAT6'
  
    UNION ALL
  
   SELECT DATEDIFF(HH,@DET,@DST) * (SELECT COUNT([EquipmentId])
   FROM RPZCBIDashboard].[dbo].[Mining_Equipment]
   Where EquipmentTypeId = @Equipment 
   and IsActive = 1)-(SELECT SUM(#TMP1.DownTime) FROM #TMP1 WHERE #TMP1.DType  &lt;&gt; 'CAT6' ), 'Productive Time'
  
  
   SELECT @DST
   SELECT @DET 
  
   SELECT @Equipment 
  </code></pre>
  
  <p>This is my SP, if I remove the comment out part where I set the variable and run it en removing the declarating of parameters at the top it return results no problem.</p>
  
  <p>But if I execute the code as follows:</p>
  
  <pre><code>DECLARE @return_value int
  
  EXEC    @return_value = [dbo].[BIDASHBOARD_MiningUtilization]
          @DST = '2014-02-01 00:00:00.000',
          @DET = '2014-02-28 00:00:00.000',
          @Equipment = 8
  
  SELECT  'Return Value' = @return_value
  
  GO
  </code></pre>
  
  <p>I only get this...</p>
  
  <pre><code>DownTime    DType
  NULL    Productive Time
  </code></pre>
  
  <p>and this message..
  Msg 3701, Level 11, State 5, Procedure BIDASHBOARD_MiningUtilization, Line 21
  Cannot drop the table '#TMP1', because it does not exist or you do not have permission.</p>
  
  <p>This is the results I get when I run the query within the Store Procedure:</p>
  
  <pre><code>DownTime    DType
  23          Equipment Not Utilized
  1130    Equipment Unavailable
  7919    Productive Time
  </code></pre>