CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
user1546143
user1546143

Reputation: 143

Using Temp table in SQL SP - Not returning Values

i Guys need your help again before I go insane...

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....

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 < @DST 
AND DownEndTime >= @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  <> '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  <> 'CAT6' ), 'Productive Time'


 SELECT @DST
 SELECT @DET 

 SELECT @Equipment

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.

But if I execute the code as follows:

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

I only get this...

DownTime    DType
NULL    Productive Time

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.

This is the results I get when I run the query within the Store Procedure:

DownTime    DType
23          Equipment Not Utilized
1130    Equipment Unavailable
7919    Productive Time

Upvotes: 0

Views: 193

Answers (3)

Rahul
Rahul

Reputation: 77934

do like drop table if exist like below instead of directly dropping it

if exists (select * from sys.objects where name = '#TMP1')
    drop table #TMP1

There ae many a mistake in your procedure as pointed below

ALTER PROCEDURE [dbo].[BIDASHBOARD_MiningUtilization] 

 @DST datetime, @DET datetime, @Equipment int

AS
BEGIN

DROP TABLE #TMP1  <-- change it as sigested in answer
CREATE TABLE #TMP1 (DownTime int, DType varchar(50));
INSERT INTO #TMP1

SELECT SUM(UTILSUM.[Minutes])/ 60 AS DOWNTIME, UTILSUM.DTYPE

FROM

(SELECT DATEDIFF(Mi,TOTAL.SDATE,TOTAL.EDATE) AS [Minutes],TOTAL.DTYPE <-- 2, no need of feching extra columns 
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] <-- 3, missing '['
 Where DownStartTime < @DST 
AND DownEndTime >= @DET
  and EquipmentID in (SELECT [EquipmentId] <-- 4, missing a space after select

  FROM [RPZCBIDashboard].[dbo].[Mining_Equipment] <-- 5, missing '['
  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  <> 'CAT6'

  UNION ALL

 SELECT DATEDIFF(HH,@DET,@DST) * (SELECT COUNT([EquipmentId])  <-- 6, remove SELECT from SELECT COUNT([EquipmentId]
 FROM RPZCBIDashboard].[dbo].[Mining_Equipment]
 Where EquipmentTypeId = @Equipment 
 and IsActive = 1)-(SELECT SUM(#TMP1.DownTime) FROM #TMP1 WHERE #TMP1.DType  <> 'CAT6' ), 'Productive Time'

Upvotes: 0

user1546143
user1546143

Reputation: 143

I have resolved the problem:

EXEC    @return_value = [dbo].[BIDASHBOARD_MiningUtilization]
        @DET = '2014-02-01 00:00:00.000',
        @DST = '2014-02-28 00:00:00.000',
        @Equipment = 8

Date ranges was wrong.

Upvotes: 0

g2server
g2server

Reputation: 5367

in your SP, try replacing this

DROP TABLE #TMP1

with this

IF OBJECT_ID('tempdb..#TMP1') IS NOT NULL DROP TABLE #TMP1

Upvotes: 1

Related Questions