SQL 2014 存储过程插入到表检查为空的表中

debugcn 发表于 Dev

MCP_infiltrator

我有一个想要每天运行的存储过程。我在存储过程中有一个约束，如果当前日期等于表中的最大日期，则它无法将数据插入表中，如下所示：

-- MAKE SURE IT WAS NOT RUN FOR THE DAY ALREADY
AND CAST(GETDATE() AS date) != (SELECT MAX(RUNDATE) FROM smsdss.c_ins_bal_amt)

在测试期间，我截断了表格。当我执行 spexec smsdss.c_ins_bal_amt_sp;然后检查结果表中的数据时，它是空白的。所以我运行了INSERT查询，如果表已经创建并且肯定没有结果，我发现这肯定是因为上述行。

我将如何纠正这一点？如果我出于某种原因在未来截断表格（这真的不应该发生......但是）

如果需要，这里是 sp 的全部内容：

/*
Check to see if the table even exists. If not create and populate, else insert
new records only if the run date is not already in the table.
*/
IF NOT EXISTS (
    SELECT TOP 1 * FROM sysobjects WHERE name='c_ins_bal_amt' AND xtype='U'
)


BEGIN
    CREATE TABLE smsdss.c_ins_bal_amt (
        PK INT IDENTITY(1, 1) PRIMARY KEY NOT NULL
        , pt_id char(13) NOT NULL
        , unit_seq_no int NULL
        , cr_rating VARchar(2) NULL
        , vst_end_date date NULL
        , fc VARchar(4) NULL
        , hosp_svc char(4) NULL
        , Age_In_Days Int NULL
        , pyr_cd varchar(6) NOT NULL
        , pyr_seq_no int NOT NULL
        , tot_chg_amt money NULL
        , tot_enc_bal_amt money NULL
        , ins_pay_amt money NULL
        , pt_bal_amt money NULL
        , Ins_Bal_Amt money NULL
        , tot_pay_amt money NULL
        , pt_pay_amt money NULL
        , GuarantorDOB date NULL
        , GuarantorFirst varchar(30) NULL
        , GuarantorLast varchar(60) NULL
        , ins1_pol_no varchar(20) NULL
        , ins2_pol_no varchar(20) NULL
        , ins3_pol_no varchar(20) NULL
        , ins4_pol_no varchar(20) NULL
        , RunDate date NOT NULL
        , RunDateTime datetime NOT NULL
        , RN INT
    )

INSERT INTO smsdss.c_ins_bal_amt

SELECT PYRPLAN.pt_id
, VST.unit_seq_no
, VST.cr_rating
, CAST(VST.vst_end_date AS date)                     AS [vst_end_date]
, VST.fc
, VST.hosp_svc
, CAST(DATEDIFF(DD, VST.VST_END_DATE, GETDATE()) AS int) AS [Age_In_Days]
, PYRPLAN.pyr_cd
, PYRPLAN.pyr_seq_no
, CAST(VST.tot_chg_amt AS money)                     AS [tot_chg_amt]
, CAST(VST.tot_bal_amt AS money)                     AS [tot_enc_bal_amt]
, CAST(VST.ins_pay_amt AS money)                     AS [ins_pay_amt]
, CAST(VST.pt_bal_amt AS money)                      AS [pt_bal_amt]
, CASE
    WHEN PYRPLAN.PYR_CD = '*' THEN 0
    ELSE CAST(PYRPLAN.tot_amt_due AS money)
    END                                                AS [Ins_Bal_Amt]
, CAST(VST.tot_pay_amt AS money) AS [tot_pay_amt]
, CAST((VST.tot_pay_amt - VST.ins_pay_amt) AS money) AS [pt_pay_amt]
, CAST(guar.GuarantorDOB as date)                    AS [GuarantorDOB]
, guar.GuarantorFirst
, guar.GuarantorLast
, vst.ins1_pol_no
, vst.ins2_pol_no
, vst.ins3_pol_no
, vst.ins4_pol_no
, [RunDate] = CAST(GETDATE() AS date)
, [RunDateTime] = GETDATE()
, [RN] = ROW_NUMBER() OVER(
    PARTITION BY PYRPLAN.PT_ID
    ORDER BY PYRPLAN.PYR_SEQ_NO
)

FROM SMSMIR.PYR_PLAN AS PYRPLAN
LEFT JOIN smsmir.vst_rpt VST
ON PYRPLAN.pt_id = VST.pt_id
        AND PYRPLAN.unit_seq_no = VST.unit_seq_no
LEFT JOIN smsdss.c_guarantor_demos_v AS GUAR
ON VST.pt_id = GUAR.pt_id
    AND VST.from_file_ind = GUAR.from_file_ind

WHERE VST.tot_bal_amt > 0
AND VST.vst_end_date IS NOT NULL
AND VST.fc not in (
    '1','2','3','4','5','6','7','8','9'
)

ORDER BY PYRPLAN.pt_id
, PYRPLAN.pyr_cd
END

ELSE
    INSERT INTO smsdss.c_ins_bal_amt
    SELECT PYRPLAN.pt_id
    , VST.unit_seq_no
    , VST.cr_rating
    , CAST(VST.vst_end_date AS date)                     AS [vst_end_date]
    , VST.fc
    , VST.hosp_svc
    , CAST(DATEDIFF(DD, VST.VST_END_DATE, GETDATE()) AS int) AS [Age_In_Days]
    , PYRPLAN.pyr_cd
    , PYRPLAN.pyr_seq_no
    , CAST(VST.tot_chg_amt AS money)                     AS [tot_chg_amt]
    , CAST(VST.tot_bal_amt AS money)                     AS [tot_enc_bal_amt]
    , CAST(VST.ins_pay_amt AS money)                     AS [ins_pay_amt]
    , CAST(VST.pt_bal_amt AS money)                      AS [pt_bal_amt]
    , CASE
        WHEN PYRPLAN.PYR_CD = '*' THEN 0
        ELSE CAST(PYRPLAN.tot_amt_due AS money)
        END                                                AS [Ins_Bal_Amt]
    , CAST(VST.tot_pay_amt AS money) AS [tot_pay_amt]
    , CAST((VST.tot_pay_amt - VST.ins_pay_amt) AS money) AS [pt_pay_amt]
    , CAST(guar.GuarantorDOB as date)                    AS [GuarantorDOB]
    , guar.GuarantorFirst
    , guar.GuarantorLast
    , vst.ins1_pol_no
    , vst.ins2_pol_no
    , vst.ins3_pol_no
    , vst.ins4_pol_no
    , [RunDate] = CAST(GETDATE() AS date)
    , [RunDateTime] = GETDATE()
    , [RN] = ROW_NUMBER() OVER(
        PARTITION BY PYRPLAN.PT_ID
        ORDER BY PYRPLAN.PYR_SEQ_NO
    )

    FROM SMSMIR.PYR_PLAN AS PYRPLAN
    LEFT JOIN smsmir.vst_rpt VST
    ON PYRPLAN.pt_id = VST.pt_id
            AND PYRPLAN.unit_seq_no = VST.unit_seq_no
    LEFT JOIN smsdss.c_guarantor_demos_v AS GUAR
    ON VST.pt_id = GUAR.pt_id
        AND VST.from_file_ind = GUAR.from_file_ind

    WHERE VST.tot_bal_amt > 0
    AND VST.vst_end_date IS NOT NULL
    AND VST.fc not in (
        '1','2','3','4','5','6','7','8','9'
    )
    -- MAKE SURE IT WAS NOT RUN FOR THE DAY ALREADY
    AND CAST(GETDATE() AS date) != (SELECT MAX(RUNDATE) FROM smsdss.c_ins_bal_amt)

    ORDER BY PYRPLAN.pt_id
    , PYRPLAN.pyr_cd
;

查尔斯·布雷塔纳

如果您将来以某种方式截断表，您不会说您希望存储过程做什么。如果你只是想让 SP 停止，那么只需在开始时在存储过程中添加一个检查

 If Not Exists (Select * from smsdss.c_ins_bal_amt) return 0

另一方面，如果您希望它忽略日期约束，请将相同的条件放入日期约束逻辑中：

WHERE VST.tot_bal_amt > 0
   AND VST.vst_end_date IS NOT NULL
   AND VST.fc not in ('1','2','3','4','5','6','7','8','9')
  -- MAKE SURE IT WAS NOT RUN FOR THE DAY ALREADY
   AND (Not exists(Select * from smsdss.c_ins_bal_amt)Or
         CAST(GETDATE() AS date) != 
           (SELECT MAX(RUNDATE) 
            FROM smsdss.c_ins_bal_amt))

或者，或者：

WHERE VST.tot_bal_amt > 0
   AND VST.vst_end_date IS NOT NULL
   AND VST.fc not in ('1','2','3','4','5','6','7','8','9')
  -- MAKE SURE IT WAS NOT RUN FOR THE DAY ALREADY
   AND CAST(GETDATE() AS date) != 
           CAST(coalesce(
              (SELECT MAX(RUNDATE FROM smsdss.c_ins_bal_amt), 
               getdate()) as date)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。