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.

Aaron Bertrand · Accepted Answer · 2012-03-11 18:11:55Z

4

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

edited Mar 11, 2012 at 18:11

answered Mar 11, 2012 at 17:34

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

9 Comments

kaj Over a year ago

+1 for conversion (which I'd already done) but also getting in the quotes

StatsViaCsh Over a year ago

@Aaron Thanks... I was working with the quote issues and saw your post... I'm pasting both in verbatim, and like KAJ's suggestion, intellisense is highlighting convert/ char as it is..

StatsViaCsh Over a year ago

@Aaron Thanks, I ran that, it needs a "t3" in the except clause, and it's good to go. (running now)

Aaron Bertrand Over a year ago

That's because your dynamic SQL isn't correct. You have defined CTEs as t1 and t2, then you reference some t3 that you haven't defined. You need to print the statement and debug it (another benefit of defining it in a variable instead of just wrapping it in EXEC()...

StatsViaCsh Over a year ago

@Aaron Right... "AS T3" defines it, no?

|

kaj · Accepted Answer · 2012-03-11 17:47:57Z

2

You probably want to cast the @maxDate to a character-type datatype so that it can be concatenated with the rest of your dynamic sql (note I'm not encouraging dynamic sql)

i.e. something like:

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

See http://msdn.microsoft.com/en-us/library/ms187928.aspx for the CONVERT options

edited Mar 11, 2012 at 17:47

answered Mar 11, 2012 at 17:26

kaj

5,2712 gold badges23 silver badges18 bronze badges

3 Comments

Aaron Bertrand Over a year ago

This doesn't really help much. This ends up being WHERE TradingDate <= 2012-03-11 which, because of implicit conversion, would actually be interpreted as 1905-06-22 against datetime/smalldatetime - you need to surround the string representation of the date with single quotes so it isn't interpreted as a mathematical expression or an integer.

kaj Over a year ago

@AaronBertrand - Good point, I'd forgotten the single quotes. I'd just focussed on trying to concatenate a date to a string without then ensuring it would be treated correctly

StatsViaCsh Over a year ago

@KAJ Thanks for the help... I was working through the quotes issue when I saw the added comments.. I copy/ pasted your edit and intellisense is highlighting "CONVERT(varchar(20)."

Collectives™ on Stack Overflow

Dynamic sql variable issues need fixing

2 Answers 2

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related