Using Temp table in SQL SP - Not returning Values

Question

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

You seem to be missing some [ characters when referring RPZCBIDashboard]. — CoolWilly
– CoolWilly, Commented Jun 17, 2014 at 13:08

g2server · Accepted Answer · 2014-06-17 12:47:15Z

1

in your SP, try replacing this

DROP TABLE #TMP1

with this

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

answered Jun 17, 2014 at 12:47

g2server

5,4173 gold badges31 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1546143 Over a year ago

Hi G2server, this is not actually causing my frustration its that if I run the SP within that window I get results but as soon as I execute the sp from outside it returns no value, but thanks for the piece of code.

g2server Over a year ago

Can you try creating the same SP (replace ALTER with CREATE) and change the name to eg. [BIDASHBOARD_MiningUtilization2] ?, then try and EXEC the new SP within the same SSMS tab ?

user1546143 Over a year ago

Meaning I must set the parameters within the SSMS tab for both procedures?

g2server Over a year ago

apologies, i had made the assumption that permissions may have been needed to CREATE a temp table... in fact there are none. (i guessed that the person who created the SP had more restrictive permissions than yours).

user1546143 · Accepted Answer · 2014-06-17 13:28:08Z

0

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.

answered Jun 17, 2014 at 13:28

user1546143

1431 silver badge11 bronze badges

1 Comment

g2server Over a year ago

I would have thought the start time would come before the end time, but glad you sorted it! :-)

Rahul · Accepted Answer · 2014-06-17 13:34:28Z

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'

Hi Rahul, thanx for the code but this is not my problem I have :)

Collectives™ on Stack Overflow

Using Temp table in SQL SP - Not returning Values

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related