Пользовательские параметры, не работающие с вложенным запросом хранимой процедуры

ProgramBox

Пользовательские параметры, не работающие с вложенным запросом хранимой процедуры

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

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

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

Вопрос:

Я создаю хранимые процедуры для создания отчетов на основе пользовательских параметров в качестве фильтров. У меня уже работает почти каждый SELECT запрос, но последний, который содержит кучу LEFT JOIN , заканчивается возвратом NULL во всей таблице.

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

 exec promediosDot
        @periodo1a = 201612,
        @periodo1b = 201703,
        @periodo2a = 201712,
        @periodo2b = 201803,
        @periodo3a = 201812,
        @periodo3b = 201903,
        @tipGas = 'VENTAS'

Это запрос:

 CREATE PROCEDURE promediosDot
    @periodo1a INT,
    @periodo1b INT,
    @periodo2a INT,
    @periodo2b INT,
    @periodo3a INT,
    @periodo3b INT,
    @tipGas NVARCHAR(6)
AS
    SELECT DISTINCT 
        T1.HOMOLOG_VP, 
        PROMEDIO1 = ((T2.CANT_PERS1   T3.CANT_PERS2   T4.CANT_PERS3   T5.CANT_PERS4) / 4),
        PROMEDIO2 = ((T6.CANT_PERS1   T7.CANT_PERS2   T8.CANT_PERS3   T9.CANT_PERS4) / 4),
        PROMEDIO3 = ((T10.CANT_PERS1   T11.CANT_PERS2   T12.CANT_PERS3   T13.CANT_PERS4) / 4)
    FROM 
        (SELECT * 
         FROM USOS_CP 
         WHERE PER_PRO > 201611) T1
    LEFT JOIN 
        (SELECT 
             HOMOLOG_VP, 
             COUNT(DISTINCT ID_HR) AS CANT_PERS1 
         FROM 
             DOTACION 
         WHERE 
             PERPRO = @periodo1a 
         GROUP BY 
             HOMOLOG_VP) T2 ON T1.HOMOLOG_VP = T2.HOMOLOG_VP
    LEFT JOIN 
        (SELECT 
             HOMOLOG_VP, COUNT(DISTINCT ID_HR) AS CANT_PERS2 
         FROM 
             DOTACION 
         WHERE 
             PERPRO = @periodo1a   1 
         GROUP BY 
             HOMOLOG_VP) T3 ON T1.HOMOLOG_VP = T3.HOMOLOG_VP
    LEFT JOIN 
        (SELECT 
             HOMOLOG_VP, COUNT(DISTINCT ID_HR) AS CANT_PERS3 
         FROM 
             DOTACION 
         WHERE 
             PERPRO = @periodo1a   2 
         GROUP BY 
             HOMOLOG_VP) T4 ON T1.HOMOLOG_VP = T4.HOMOLOG_VP
    LEFT JOIN 
        (SELECT 
             HOMOLOG_VP, COUNT(DISTINCT ID_HR) AS CANT_PERS4 
         FROM 
             DOTACION 
         WHERE 
             PERPRO = @periodo1a   3 
         GROUP BY 
             HOMOLOG_VP) T5 ON T1.HOMOLOG_VP = T5.HOMOLOG_VP
    --
    LEFT JOIN 
        (SELECT 
             HOMOLOG_VP, COUNT(DISTINCT ID_HR) AS CANT_PERS1 
         FROM 
             DOTACION 
         WHERE 
             PERPRO = @periodo2a 
         GROUP BY 
             HOMOLOG_VP) T6 ON T1.HOMOLOG_VP = T6.HOMOLOG_VP
    LEFT JOIN 
        (SELECT 
             HOMOLOG_VP, COUNT(DISTINCT ID_HR) AS CANT_PERS2
         FROM 
             DOTACION 
         WHERE 
             PERPRO = @periodo2a   1
         GROUP BY 
             HOMOLOG_VP) T7 ON T1.HOMOLOG_VP = T7.HOMOLOG_VP
    LEFT JOIN 
        (SELECT 
             HOMOLOG_VP, COUNT(DISTINCT ID_HR) AS CANT_PERS3
         FROM 
             DOTACION 
         WHERE 
             PERPRO = @periodo2a   2
         GROUP BY 
             HOMOLOG_VP) T8 ON T1.HOMOLOG_VP = T8.HOMOLOG_VP
    LEFT JOIN 
        (SELECT 
             HOMOLOG_VP, COUNT(DISTINCT ID_HR) AS CANT_PERS4
         FROM 
             DOTACION 
         WHERE 
             PERPRO = @periodo2a   3
         GROUP BY 
             HOMOLOG_VP) T9 ON T1.HOMOLOG_VP = T9.HOMOLOG_VP
    --
    LEFT JOIN 
        (SELECT 
             HOMOLOG_VP, COUNT(DISTINCT ID_HR) AS CANT_PERS1
         FROM 
             DOTACION 
         WHERE 
             PERPRO = @periodo3a
         GROUP BY 
             HOMOLOG_VP) T10 ON T1.HOMOLOG_VP = T10.HOMOLOG_VP   
     LEFT JOIN 
        (SELECT 
             HOMOLOG_VP, COUNT(DISTINCT ID_HR) AS CANT_PERS2
         FROM 
             DOTACION 
         WHERE 
             PERPRO = @periodo3a   1
         GROUP BY 
             HOMOLOG_VP) T11 ON T1.HOMOLOG_VP = T11.HOMOLOG_VP   
    LEFT JOIN 
        (SELECT 
             HOMOLOG_VP, COUNT(DISTINCT ID_HR) AS CANT_PERS3
         FROM 
             DOTACION 
         WHERE 
             PERPRO = @periodo3a   2
         GROUP BY 
             HOMOLOG_VP) T12 ON T1.HOMOLOG_VP = T12.HOMOLOG_VP   
    LEFT JOIN
        (SELECT 
             HOMOLOG_VP, COUNT(DISTINCT ID_HR) AS CANT_PERS4
         FROM 
             DOTACION 
         WHERE 
             PERPRO = @periodo3a   3
         GROUP BY 
             HOMOLOG_VP) T13 ON T1.HOMOLOG_VP = T13.HOMOLOG_VP   
GO

Результаты (извлечение):

 HOMOLOG_VP  PROMEDIO1   PROMEDIO2   PROMEDIO3
---------------------------------------------
a3          NULL    NULL    NULL
a4          NULL    NULL    NULL

Я не уверен, что это просто природа параметров, которые не работают до определенного момента или не работают над вложенным запросом. Запрос работает отлично, если параметры записываются вручную. Любая помощь приветствуется.

Ответ №1:

Проблема может возникнуть из-за того, что ваш период установлен на декабрь как 201612, и если вы добавите его, он станет 201613 вместо 201701. Это проблема использования целых чисел для значений даты. Как упоминал Шон, это также можно преобразовать в более простой запрос.

 CREATE PROCEDURE promediosDot
        @periodo1a int,
        @periodo1b int,
        @periodo2a int,
        @periodo2b int,
        @periodo3a int,
        @periodo3b int,
        @tipGas nvarchar(6)

AS
SELECT  
    U.HOMOLOG_VP, 
    PROMEDIO1 = (   COUNT(DISTINCT CASE WHEN D.PERPRO = CONVERT( CHAR(6), DATEADD( MM, 0, CAST( @periodo1a AS VARCHAR(8))   '01'), 112)  THEN D.ID_HR END) 
                    COUNT(DISTINCT CASE WHEN D.PERPRO = CONVERT( CHAR(6), DATEADD( MM, 1, CAST( @periodo1a AS VARCHAR(8))   '01'), 112)  THEN D.ID_HR END)
                    COUNT(DISTINCT CASE WHEN D.PERPRO = CONVERT( CHAR(6), DATEADD( MM, 2, CAST( @periodo1a AS VARCHAR(8))   '01'), 112)  THEN D.ID_HR END)
                    COUNT(DISTINCT CASE WHEN D.PERPRO = CONVERT( CHAR(6), DATEADD( MM, 3, CAST( @periodo1a AS VARCHAR(8))   '01'), 112)  THEN D.ID_HR END)) /4,
    PROMEDIO2 = (   COUNT(DISTINCT CASE WHEN D.PERPRO = CONVERT( CHAR(6), DATEADD( MM, 0, CAST( @periodo2a AS VARCHAR(8))   '01'), 112)  THEN D.ID_HR END) 
                    COUNT(DISTINCT CASE WHEN D.PERPRO = CONVERT( CHAR(6), DATEADD( MM, 1, CAST( @periodo2a AS VARCHAR(8))   '01'), 112)  THEN D.ID_HR END)
                    COUNT(DISTINCT CASE WHEN D.PERPRO = CONVERT( CHAR(6), DATEADD( MM, 2, CAST( @periodo2a AS VARCHAR(8))   '01'), 112)  THEN D.ID_HR END)
                    COUNT(DISTINCT CASE WHEN D.PERPRO = CONVERT( CHAR(6), DATEADD( MM, 3, CAST( @periodo2a AS VARCHAR(8))   '01'), 112)  THEN D.ID_HR END)) /4,
    PROMEDIO3 = (   COUNT(DISTINCT CASE WHEN D.PERPRO = CONVERT( CHAR(6), DATEADD( MM, 0, CAST( @periodo3a AS VARCHAR(8))   '01'), 112)  THEN D.ID_HR END) 
                    COUNT(DISTINCT CASE WHEN D.PERPRO = CONVERT( CHAR(6), DATEADD( MM, 1, CAST( @periodo3a AS VARCHAR(8))   '01'), 112)  THEN D.ID_HR END)
                    COUNT(DISTINCT CASE WHEN D.PERPRO = CONVERT( CHAR(6), DATEADD( MM, 2, CAST( @periodo3a AS VARCHAR(8))   '01'), 112)  THEN D.ID_HR END)
                    COUNT(DISTINCT CASE WHEN D.PERPRO = CONVERT( CHAR(6), DATEADD( MM, 3, CAST( @periodo3a AS VARCHAR(8))   '01'), 112)  THEN D.ID_HR END)) /4
FROM USOS_CP  U
JOIN DOTACION D ON U.HOMOLOG_VP = D.HOMOLOG_VP
WHERE U.PER_PRO > 201611
AND (D.PERPRO BETWEEN @periodo1a AND @periodo1a   3
    OR D.PERPRO BETWEEN @periodo2a AND @periodo2a   3
    OR D.PERPRO BETWEEN @periodo3a AND @periodo3a   3)
GROUP BY U.HOMOLOG_VP
ORDER BY HOMOLOG_VP;

Ответ №2:

У вас есть несколько параметров, которые не используются в опубликованном вами коде. Но вы могли бы значительно упростить этот запрос. Что-то вроде этого должно быть то же самое. Нет необходимости снова и снова присоединяться к одной и той же таблице.

 SELECT cp.HOMOLOG_VP
    , PROMEDIO1 = count(case when d.PERPRO >= @periodo1a and d.PERPRO <= @periodo1a   3 then 1 end) / 4
    , PROMEDIO2 = count(case when d.PERPRO >= @periodo2a and d.PERPRO <= @periodo2a   3 then 1 end) / 4
    , PROMEDIO3 = count(case when d.PERPRO >= @periodo3a and d.PERPRO <= @periodo3a   3 then 1 end) / 4
FROM USOS_CP cp
left join DOTACION d on d.HOMOLOG_VP = cp.HOMOLOG_VP
WHERE cp.PER_PRO > 201611
group by cp.HOMOLOG_VP

1. Я думал сделать что-то подобное, но я не уверен, что это сработает, потому что OP использует COUNT DISTINCT .

2. Да, у процедуры есть еще 2 запроса, которые используют эти переменные после того, который я опубликовал. Я знаю, что мой запрос — это мусор, но мне нужно каждый раз различать идентификаторы (период), а также ваши циклы вечно.

3. Не удалось протестировать это, потому что мы не получили определения таблиц или образцы данных. Это был мой выстрел в темноте. Ну что ж… в следующий раз опубликуйте определения таблиц, и людям будет легче помочь.

Метки: Пользовательские параметры не работающие с вложенным запросом хранимой процедуры