Dynamic sql variable issues need fixing

Question

I made the seemingly minor change of adding a 2nd variable to a stored procedure in sql server 2008 r2 that uses dynamic sql, and I'm getting a new error:

Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int

when I run the next code block below (the one following that is my sproc)

DECLARE @tableName varchar(120)
SET @tableName = 'tblDailySMA'
DECLARE @mxDate DATE
SET @mxDate = dbo.LatestDateWithPricingVolCountOver4k()
EXEC sprocAddDatesSymbolsAndPeriodsToAggregatedStudy @tableName, @mxDate

USE [Market]
GO
/****** Object:  StoredProcedure [dbo].[sprocAddDatesSymbolsAndPeriodsToAggregatedStudy]    Script Date: 03/11/2012 12:55:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocAddDatesSymbolsAndPeriodsToAggregatedStudy]
    @table varchar(120), @maxDate DATE

AS

EXEC(
    ';WITH t1 AS
    (
        SELECT Symbol, TradingDate
        FROM tblSymbolsMain
        CROSS JOIN tblTradingDays
        WHERE TradingDate <=' + @maxDate +
    '), 

    t2 AS
    (
        SELECT Symbol, TradingDate, Period
        FROM t1
        CROSS JOIN tblPeriods
    )

    INSERT INTO ' + @table + ' (Symbol, TradeDate, Period)
    (SELECT Symbol, TradingDate, Period
    FROM t2
    EXCEPT
        (SELECT t3.Symbol, t3.TradeDate, t3.Period
         FROM ' + @table + '))')

RETURN

I'm sure it's a very simple quick fix, what am I overlooking? Thanks in advance.

Answer 1

You need to surround date parameters with single quotes. And to embed those in dynamic SQL you need to escape them.

'... WHERE TradingDate <= ''' + CONVERT(CHAR(8), @maxDate, 112) + ''' ...'

Or to make it a little easier to read (depending on the person, I guess):

'... WHERE TradingDate <= ' + CHAR(39) + CONVERT(CHAR(8), @maxDate, 112) + CHAR(39) + ' ...'

Perhaps the other issue is that you don't want to concatenate and call convert and other built-ins inside EXEC, how about:

ALTER PROCEDURE [dbo].[sprocAddDatesSymbolsAndPeriodsToAggregatedStudy]
    @table   VARCHAR(120), 
    @maxDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX) = N';WITH t1 AS
    (
        SELECT Symbol, TradingDate
        FROM tblSymbolsMain
        CROSS JOIN tblTradingDays
        WHERE TradingDate <= ''' + CONVERT(CHAR(8), @maxDate, 112) +
    '''), 
    t2 AS
    (
        SELECT Symbol, TradingDate, Period
        FROM t1
        CROSS JOIN tblPeriods
    )
    INSERT INTO ' + @table + ' (Symbol, TradeDate, Period)
    (SELECT Symbol, TradingDate, Period
    FROM t2
    EXCEPT
        (SELECT t3.Symbol, t3.TradeDate, t3.Period
         FROM ' + @table + '))';

    EXEC sp_executesql @sql;
END
GO