Declaring variables in dynamic SQL for Database Mail

Question

I have a template that I use for all my database mail jobs that are emailed and I've stumbled upon an issue with my latest one which arises from the use of variables, here's the code:

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 = '[email protected]',
    @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

So what this code does is basically is runs a query, if row count is greater than 0, then the second part executes and the emails in the recipient list get an email. Now I know there's nothing wrong with the first part because I've tested it, the issue lies with the BEGIN statement after:

SELECT @CODE_ROW_COUNT = @@ROWCOUNT

IF(@CODE_ROW_COUNT > 0)

I read that the dynamic part has its own scope, so I just tried declaring all the variables there, but I keep getting syntax errors, not sure how to proceed. I don't know if I have a simple syntax error or if I have to do some sort of workaround to get it to work. Any help is appreciated, thanks

Edit: This is the errors I'm getting:

Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'DECLARE'. Msg 102, Level 15, State 1, Line 114 Incorrect syntax near ')'.

Declaring your variables like that seems to defeat the purpose since you just hardcode the value. But you can pass parameters to dynamic sql. You have to sp_executesql Debugging dynamic sql can be challenging. You should print/select your dynamic code to evaluate it BEFORE you execute it. — Sean Lange
– Sean Lange, Commented Jan 31, 2019 at 21:58
I'm with @SeanLange, why use variables here? All the IF statements don't have an ELSE so it's like the only value you will ever use is @WorkI = 100 — S3S
– S3S, Commented Jan 31, 2019 at 22:02
It's set up so I only have to change @ WorkI and it automatically updates @ WorkC and @ DailyMin everywhere in the code, helps prevent errors as I set this up for all the work centers. I did try hard coding everything actually and I was still having syntax issues with the WITH part in the dynamic part. All of other Database Mail jobs I've ever set up are regular select statements, so this is the first time I've encountered these kinds of issues — Cristian Contreras
– Cristian Contreras, Commented Jan 31, 2019 at 22:08
May I suggest set @WorkC = case @WorkI when '100' then 'Cutting' when '125' then 'Framing' ... else 'default value here';? (Extra credit given for tidy formatting.) — HABO
– HABO, Commented Feb 1, 2019 at 3:54
@HABO Thanks, that is much cleaner indeed, just made the change and works great — Cristian Contreras
– Cristian Contreras, Commented Feb 1, 2019 at 18:56

S3S · Accepted Answer · 2019-01-31 22:08:58Z

1

The last part of your code before the dynamic SQL you are selecting the final results into a global temp.

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

Since you are doing this, you can just change @code to the results of the query from the temp table so you don't have to repeat and re-query every table again.

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

answered Jan 31, 2019 at 22:08

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cristian Contreras Over a year ago

Wow, I'm an idiot, thanks so much!!! Works absolutely perfect now, just ran it and received my email as expected. I hadn't realized I could use the global temp table for the dynamic part

S3S Over a year ago

No sweat @CristianContreras. The scope of temp tables are to the session that created them. Global temp tables can be accessed by almost any session as long as it hasn't been dropped by the creator session (and references don't exist)

Collectives™ on Stack Overflow

Declaring variables in dynamic SQL for Database Mail

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related