为订阅的报表创建存储过程

Question

我正在尝试创建一个通知系统，当在我们的ERP数据库中设置错误时，它会提醒我。我有一个报告，我必须手动运行每天检查，看看是否有什么需要我解决。我不想这样做，我希望把报告通过电子邮件发送给我，或者发送某种通知给我。

有人建议如下：

• 在SSRS中创建报告(已完成；如果报告没有任何数据，如果有数据，则没有什么可修复的，我需要修复某些内容)
• 创建一个存储过程，如果没有数据，该过程将引发错误。
• SSRS报告将使用存储过程数据集。
• 订阅报告，只有当数据存在时，它才会发送电子邮件。

第二部分是存储过程，下面是我的代码：

CREATE PROCEDURE JOBNOMATS
AS
SELECT CASE 
            WHEN (SELECT "JobHead"."JobNum", "JobHead"."Company", "JobHead"."PartDescription", "JobHead"."ReqDueDate", "JobMtl"."PartNum", "JobMtl"."MtlSeq", "JobHead"."InCopyList", "JobHead"."Plant"
            FROM   {oj "Epicor10Live"."Erp"."JobHead" "JobHead" LEFT OUTER JOIN "Epicor10Live"."Erp"."JobMtl" "JobMtl" ON ("JobHead"."Company"="JobMtl"."Company") AND ("JobHead"."JobNum"="JobMtl"."JobNum")}
            WHERE  "JobHead"."Company"='011' AND "JobMtl"."PartNum" IS  NULL  AND  NOT ("JobHead"."JobNum"='95057' OR "JobHead"."JobNum"='AISMNJOB')) IS NULL 
THEN RAISERROR(50001,16,1) 
 ELSE SELECT "JobHead"."JobNum", "JobHead"."Company", "JobHead"."PartDescription", "JobHead"."ReqDueDate", "JobMtl"."PartNum", "JobMtl"."MtlSeq", "JobHead"."InCopyList", "JobHead"."Plant"
            FROM   {oj "Epicor10Live"."Erp"."JobHead" "JobHead" LEFT OUTER JOIN "Epicor10Live"."Erp"."JobMtl" "JobMtl" ON ("JobHead"."Company"="JobMtl"."Company") AND ("JobHead"."JobNum"="JobMtl"."JobNum")}
            WHERE  "JobHead"."Company"='011' AND "JobMtl"."PartNum" IS  NULL  AND  NOT ("JobHead"."JobNum"='95057' OR "JobHead"."JobNum"='AISMNJOB')

但我明白这两个错误：

Msg 156，第15级，状态1，过程JOBNOMATS，第7行在关键字'RAISERROR‘附近的不正确语法。

Msg 156，第15级，状态1，过程JOBNOMATS，第9行不正确的语法靠近关键字‘Level’。

Answer 1

你不能在案件陈述中这么做。你可以试试这样的..。

CREATE PROCEDURE JOBNOMATS
AS
BEGIN
    IF NOT EXISTS(
        SELECT NULL
        FROM   {oj "Epicor10Live"."Erp"."JobHead" "JobHead" 
            LEFT OUTER JOIN "Epicor10Live"."Erp"."JobMtl" "JobMtl" ON 
                ("JobHead"."Company"="JobMtl"."Company") 
            AND ("JobHead"."JobNum"="JobMtl"."JobNum")}
        WHERE  "JobHead"."Company"='011' AND "JobMtl"."PartNum" IS NULL  
        AND  NOT ("JobHead"."JobNum"='95057' OR "JobHead"."JobNum"='AISMNJOB')
    )
    BEGIN
        RAISERROR(50001,16,1);
    END

    SELECT "JobHead"."JobNum", "JobHead"."Company", "JobHead"."PartDescription", "JobHead"."ReqDueDate", "JobMtl"."PartNum", "JobMtl"."MtlSeq", "JobHead"."InCopyList", "JobHead"."Plant"
    FROM   {oj "Epicor10Live"."Erp"."JobHead" "JobHead" LEFT OUTER JOIN "Epicor10Live"."Erp"."JobMtl" "JobMtl" ON ("JobHead"."Company"="JobMtl"."Company") AND ("JobHead"."JobNum"="JobMtl"."JobNum")}
    WHERE  "JobHead"."Company"='011' AND "JobMtl"."PartNum" IS  NULL  AND  NOT ("JobHead"."JobNum"='95057' OR "JobHead"."JobNum"='AISMNJOB')

END

有更好的方法来做到这一点。运行两次相同的查询有点浪费。

还可以在查询执行后检查@@ROWCOUNT，如果查询为0，则引发错误

CREATE PROCEDURE JOBNOMATS
AS
BEGIN
    SELECT "JobHead"."JobNum", "JobHead"."Company", "JobHead"."PartDescription", "JobHead"."ReqDueDate", "JobMtl"."PartNum", "JobMtl"."MtlSeq", "JobHead"."InCopyList", "JobHead"."Plant"
    FROM   {oj "Epicor10Live"."Erp"."JobHead" "JobHead" LEFT OUTER JOIN "Epicor10Live"."Erp"."JobMtl" "JobMtl" ON ("JobHead"."Company"="JobMtl"."Company") AND ("JobHead"."JobNum"="JobMtl"."JobNum")}
    WHERE  "JobHead"."Company"='011' AND "JobMtl"."PartNum" IS  NULL  AND  NOT ("JobHead"."JobNum"='95057' OR "JobHead"."JobNum"='AISMNJOB')

    IF @@ROWCOUNT = 0
        RAISERROR(50001,16,1);

END

或

还可以将结果选择到临时表中，如果临时表中的记录计数为0，则引发错误.否则从临时表中选择所有东西..。

CREATE PROCEDURE JOBNOMATS
AS
BEGIN
    SELECT "JobHead"."JobNum", "JobHead"."Company", "JobHead"."PartDescription", "JobHead"."ReqDueDate", "JobMtl"."PartNum", "JobMtl"."MtlSeq", "JobHead"."InCopyList", "JobHead"."Plant"
    INTO #TEMP
    FROM   {oj "Epicor10Live"."Erp"."JobHead" "JobHead" LEFT OUTER JOIN "Epicor10Live"."Erp"."JobMtl" "JobMtl" ON ("JobHead"."Company"="JobMtl"."Company") AND ("JobHead"."JobNum"="JobMtl"."JobNum")}
    WHERE  "JobHead"."Company"='011' AND "JobMtl"."PartNum" IS  NULL  AND  NOT ("JobHead"."JobNum"='95057' OR "JobHead"."JobNum"='AISMNJOB')

    IF (SELECT COUNT(1) FROM #TEMP) = 0
        RAISERROR(50001,16,1);

    --DO MORE STUFF IF YOU WANT TO...

    SELECT *
    FROM #TEMP
END

这都取决于查询的代价。