sql-server - 在数据库邮件的动态 SQL 中声明变量

Question

我有一个模板，用于所有通过电子邮件发送的数据库邮件作业，我偶然发现了我最新的一个问题，该问题是由于使用变量而引起的，代码如下：

SET QUOTED_IDENTIFIER ON


DECLARE @CODE NVARCHAR(MAX),
        @CODE_ROW_COUNT INT,
        @s VARCHAR(MAX),
        @WorkI NVARCHAR(MAX),
        @WorkC NVARCHAR(MAX),
        @DailyMin INT

SET @s = 'Daily Cutting Schedule for ' + DATENAME(dw, GETDATE()) + ', ' + CONVERT(VARCHAR(12),GETDATE(),107)

IF OBJECT_ID('tempdb..##DailyCutSched') IS NOT NULL
    BEGIN
        DROP TABLE ##DailyCutSched
    END

SET @WorkI = '100'

IF @WorkI = '100'
BEGIN
SET @WorkC = 'Cutting'
END
IF @WorkI = '125'
BEGIN
SET @WorkC = 'Framing'
END
IF @WorkI = '150'
BEGIN
SET @WorkC = 'Assembly'
END
IF @WorkI = '200'
BEGIN
SET @WorkC = 'Grinding'
END
IF @WorkI = '350'
BEGIN
SET @WorkC = 'Painting'
END
IF @WorkI = '400'
BEGIN
SET @WorkC = 'Glazing'
END
IF @WorkI = '450'
BEGIN
SET @WorkC = 'Locknprep'
END
IF @WorkI = '500'
BEGIN
SET @WorkC = 'Packaging'
END;

SET @DailyMin = 
(SELECT
    w.CapacityFactor*(w.UtilizationPct/100)*7.67*60 AS [Units]
FROM WorkCntr w
WHERE w.WorkCntr = @WorkI);

WITH CTE AS
(
    SELECT TOP 10000
        ds.WorkCntr,
        ds.JobNo,
        r.PartNo,
        r.StepNo AS [Step],
        CAST(o.PartDesc AS NVARCHAR(MAX)) AS [Description],
        r.CycleTime*o.QtyOrdered AS [Estimate],
        SUM(r.CycleTime*o.QtyOrdered) OVER (PARTITION BY ds.WorkCntr 
        ORDER BY o.Priority DESC, 
        CAST(SUBSTRING(r.PartNo, CHARINDEX('.',r.PartNo)+1, CHARINDEX('.',r.PartNo+'.',CHARINDEX('.',r.PartNo)+1) - CHARINDEX('.',r.PartNo)-1) as INT),
        r.PartNo) AS [CumulativeMinutes],
        o.Priority
    FROM
        Scheduling ds
        JOIN dbo.OrderRouting r ON ds.JobNo = r.JobNo AND ds.WorkCntr = @WorkC
        JOIN dbo.OrderDet o ON ds.JobNo = o.JobNo
        LEFT JOIN dbo.TimeTicketDet t ON ds.JobNo = t.JobNo AND t.WorkCntr = @WorkI
    WHERE 
        r.WorkCntr = @WorkC
        AND r.OrderNo NOT IN ('44444', '77777')
    GROUP BY ds.WorkCntr, ds.JobNo, r.PartNo, r.StepNo, CAST(o.PartDesc AS NVARCHAR(MAX)), r.CycleTime, o.QtyOrdered, o.Priority
    ORDER BY o.Priority DESC, CAST(SUBSTRING(r.PartNo, CHARINDEX('.',r.PartNo)+1, CHARINDEX('.',r.PartNo+'.',CHARINDEX('.',r.PartNo)+1) - CHARINDEX('.',r.PartNo)-1) as INT), r.PartNo
),

CTE2 AS(

SELECT
    c.WorkCntr,
    c.JobNo,
    CAST(SUBSTRING(c.PartNo, CHARINDEX('.',c.PartNo)+1, CHARINDEX('.',c.PartNo+'.',CHARINDEX('.',c.PartNo)+1) - CHARINDEX('.',c.PartNo)-1) as INT) AS [ItemNo],
    c.PartNo,
    c.Step,
    c.Description,
    c.Estimate,
    c.Priority
FROM CTE c
WHERE CumulativeMinutes < @DailyMin

UNION

SELECT TOP 1
    c.WorkCntr,
    c.JobNo,
    CAST(SUBSTRING(c.PartNo, CHARINDEX('.',c.PartNo)+1, CHARINDEX('.',c.PartNo+'.',CHARINDEX('.',c.PartNo)+1) - CHARINDEX('.',c.PartNo)-1) as INT) AS [ItemNo],
    c.PartNo,
    c.Step,
    c.Description,
    c.Estimate,
    c.Priority
FROM CTE c
WHERE CumulativeMinutes > @DailyMin
ORDER BY c.Priority DESC, CAST(SUBSTRING(c.PartNo, CHARINDEX('.',c.PartNo)+1, CHARINDEX('.',c.PartNo+'.',CHARINDEX('.',c.PartNo)+1) - CHARINDEX('.',c.PartNo)-1) as INT)

)

SELECT 
    c2.WorkCntr,
    c2.JobNo,
    c2.PartNo,
    c2.Step,
    c2.Description,
    c2.Estimate
INTO ##DailyCutSched
FROM CTE2 c2
ORDER BY c2.Priority DESC, CAST(SUBSTRING(c2.PartNo, CHARINDEX('.',c2.PartNo)+1, CHARINDEX('.',c2.PartNo+'.',CHARINDEX('.',c2.PartNo)+1) - CHARINDEX('.',c2.PartNo)-1) as INT), c2.PartNo

SELECT @CODE_ROW_COUNT = @@ROWCOUNT

IF(@CODE_ROW_COUNT > 0)

BEGIN

SET @CODE =
N'
DECLARE @WorkI NVARCHAR(MAX) = ''100''
DECLARE @WorkC NVARCHAR(MAX)
DECLARE @DailyMin INT

IF @WorkI = ''100''
BEGIN
SET @WorkC = ''Cutting''
END
IF @WorkI = ''125''
BEGIN
SET @WorkC = ''Framing''
END
IF @WorkI = ''150''
BEGIN
SET @WorkC = ''Assembly''
END
IF @WorkI = ''200''
BEGIN
SET @WorkC = ''Grinding''
END
IF @WorkI = ''350''
BEGIN
SET @WorkC = ''Painting''
END
IF @WorkI = ''400''
BEGIN
SET @WorkC = ''Glazing''
END
IF @WorkI = ''450''
BEGIN
SET @WorkC = ''Locknprep''
END
IF @WorkI = ''500''
BEGIN
SET @WorkC = ''Packaging''
END;

SET @DailyMin = 
(SELECT
    w.CapacityFactor*(w.UtilizationPct/100)*7.67*60 AS [Units]
FROM WorkCntr w
WHERE w.WorkCntr = @WorkI);

WITH CTE AS
(
    SELECT TOP 10000
        ds.WorkCntr,
        ds.JobNo,
        r.PartNo,
        r.StepNo AS [Step],
        CAST(o.PartDesc AS NVARCHAR(MAX)) AS [Description],
        r.CycleTime*o.QtyOrdered AS [Estimate],
        SUM(r.CycleTime*o.QtyOrdered) OVER (PARTITION BY ds.WorkCntr 
        ORDER BY o.Priority DESC, 
        CAST(SUBSTRING(r.PartNo, CHARINDEX(''.'',r.PartNo)+1, CHARINDEX(''.'',r.PartNo+''.'',CHARINDEX(''.'',r.PartNo)+1) - CHARINDEX(''.'',r.PartNo)-1) as INT),
        r.PartNo) AS [CumulativeMinutes],
        o.Priority
    FROM
        Scheduling ds
        JOIN dbo.OrderRouting r ON ds.JobNo = r.JobNo AND ds.WorkCntr = @WorkC
        JOIN dbo.OrderDet o ON ds.JobNo = o.JobNo
        LEFT JOIN dbo.TimeTicketDet t ON ds.JobNo = t.JobNo AND t.WorkCntr = @WorkI
    WHERE 
        r.WorkCntr = @WorkC
        AND r.OrderNo NOT IN (''44444'', ''77777'')
    GROUP BY ds.WorkCntr, ds.JobNo, r.PartNo, r.StepNo, CAST(o.PartDesc AS NVARCHAR(MAX)), r.CycleTime, o.QtyOrdered, o.Priority
    ORDER BY o.Priority DESC, CAST(SUBSTRING(r.PartNo, CHARINDEX(''.'',r.PartNo)+1, CHARINDEX(''.'',r.PartNo+''.'',CHARINDEX(''.'',r.PartNo)+1) - CHARINDEX(''.'',r.PartNo)-1) as INT), r.PartNo
),

CTE2 AS(

SELECT
    c.WorkCntr,
    c.JobNo,
    CAST(SUBSTRING(c.PartNo, CHARINDEX(''.'',c.PartNo)+1, CHARINDEX(''.'',c.PartNo+''.'',CHARINDEX(''.'',c.PartNo)+1) - CHARINDEX(''.'',c.PartNo)-1) as INT) AS [ItemNo],
    c.PartNo,
    c.Step,
    c.Description,
    c.Estimate,
    c.Priority
FROM CTE c
WHERE CumulativeMinutes < @DailyMin

UNION

SELECT TOP 1
    c.WorkCntr,
    c.JobNo,
    CAST(SUBSTRING(c.PartNo, CHARINDEX(''.'',c.PartNo)+1, CHARINDEX(''.'',c.PartNo+''.'',CHARINDEX(''.'',c.PartNo)+1) - CHARINDEX(''.'',c.PartNo)-1) as INT) AS [ItemNo],
    c.PartNo,
    c.Step,
    c.Description,
    c.Estimate,
    c.Priority
FROM CTE c
WHERE CumulativeMinutes > @DailyMin
ORDER BY c.Priority DESC, CAST(SUBSTRING(c.PartNo, CHARINDEX(''.'',c.PartNo)+1, CHARINDEX(''.'',c.PartNo+''.'',CHARINDEX(''.'',c.PartNo)+1) - CHARINDEX(''.'',c.PartNo)-1) as INT)

)

SELECT 
    c2.WorkCntr,
    c2.JobNo,
    c2.PartNo,
    c2.Step,
    c2.Description,
    c2.Estimate
FROM CTE2 c2
ORDER BY c2.Priority DESC, CAST(SUBSTRING(c2.PartNo, CHARINDEX(''.'',c2.PartNo)+1, CHARINDEX(''.'',c2.PartNo+''.'',CHARINDEX(''.'',c2.PartNo)+1) - CHARINDEX(''.'',c2.PartNo)-1) as INT), c2.PartNo'

DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable 
@html = @html OUTPUT,  
@query = @CODE

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Sample Company',
    @recipients = '[e-mail]',
    @subject = @s,
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0,
    @execute_query_database = 'Database1';

END

IF OBJECT_ID('tempdb..##DailyCutSched') IS NOT NULL
    BEGIN
        DROP TABLE ##DailyCutSched
    END

所以这段代码所做的基本上是运行一个查询，如果行数大于 0，则执行第二部分，收件人列表中的电子邮件会收到一封电子邮件。现在我知道第一部分没有问题，因为我已经对其进行了测试，问题在于之后的 BEGIN 语句：

SELECT @CODE_ROW_COUNT = @@ROWCOUNT

IF(@CODE_ROW_COUNT > 0)

我读到动态部分有自己的范围，所以我只是尝试在那里声明所有变量，但我不断收到语法错误，不知道如何继续。我不知道我是否有一个简单的语法错误，或者我是否必须采取某种解决方法才能使其正常工作。任何帮助表示赞赏，谢谢

编辑：这是我得到的错误：

消息 156，级别 15，状态 1，第 6 行关键字“DECLARE”附近的语法不正确。消息 102，级别 15，状态 1，第 114 行 ')' 附近的语法不正确。

标签： sql-servertsql

Answer 1

动态 SQL 之前代码的最后一部分，您将最终结果选择到全局临时文件中。

SELECT 
    c2.WorkCntr,
    c2.JobNo,
    c2.PartNo,
    c2.Step,
    c2.Description,
    c2.Estimate
INTO ##DailyCutSched
FROM CTE2 c2
ORDER BY c2.Priority DESC, CAST(SUBSTRING(c2.PartNo, CHARINDEX('.',c2.PartNo)+1, CHARINDEX('.',c2.PartNo+'.',CHARINDEX('.',c2.PartNo)+1) - CHARINDEX('.',c2.PartNo)-1) as INT), c2.PartNo

由于您正在执行此操作，因此您只需更改@code临时表中的查询结果，就不必重复和重新查询每个表。

SET @CODE =
N'SELECT 
        c2.WorkCntr,
        c2.JobNo,
        c2.PartNo,
        c2.Step,
        c2.Description,
        c2.Estimate
    FROM ##DailyCutSched c2'