Использование временной таблицы в SQL SP - не возвращает значения

ProgramBox

Использование временной таблицы в SQL SP — не возвращает значения

Post author:admin
Запись опубликована:14 февраля, 2023
Post category:Вопросы по программированию

#sql #sql-server #sql-server-2008 #stored-procedures

#sql #sql-server #sql-server-2008 #хранимые процедуры

Вопрос:

мне, ребята, снова нужна ваша помощь, прежде чем я сойду с ума…

Теперь у меня есть следующий SP, если я выполняю его в SP, он работает нормально, но как только я выполняю sp, я не получаю результатов….

 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

Это мой SP, если я удалю часть комментария, в которой я устанавливаю переменную и запускаю ее, удаляя объявление параметров вверху, она возвращает результаты без проблем.

Но если я выполню код следующим образом:

 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

Я получаю только это…

 DownTime    DType
NULL    Productive Time

и это сообщение..
Сообщение 3701, уровень 11, состояние 5, процедура BIDASHBOARD_MiningUtilization, строка 21
Невозможно удалить таблицу ‘#TMP1’, потому что она не существует или у вас нет разрешения.

Это результаты, которые я получаю, когда запускаю запрос в процедуре хранения:

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

Ответ №1:

в вашем SP попробуйте заменить это

 DROP TABLE #TMP1

с помощью этого

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

1. Привет, G2server, на самом деле это не вызывает моего разочарования, потому что, если я запускаю SP в этом окне, я получаю результаты, но как только я выполняю sp извне, он не возвращает значения, но спасибо за фрагмент кода.

2. Можете ли вы попробовать создать тот же SP (заменить ALTER на CREATE) и изменить имя на eg. [BIDASHBOARD_MiningUtilization2] ?, затем попробуйте ВЫПОЛНИТЬ новый SP на той же вкладке SSMS?

3. Это означает, что я должен установить параметры на вкладке SSMS для обеих процедур?

4. прошу прощения, я предположил, что для СОЗДАНИЯ временной таблицы могли потребоваться разрешения… на самом деле их нет. (я предположил, что у человека, который создал SP, были более ограничительные разрешения, чем у вас).

Ответ №2:

Я решил проблему:

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

Неверные диапазоны дат.

Ответ №3:

сделайте drop table if exist , как показано ниже, вместо того, чтобы напрямую удалять его

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

В вашей процедуре много ошибок, как указано ниже

 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'