Issue with dynamic query and datepart SQL

Question

I'm getting the error

Invalid parameter 1 specified for dateadd.

when I try to execute the following dynamic parametrized query in SQL Server 2012:

DECLARE @Interval nvarchar(5) = 'DAY'  
DECLARE @Increment int = 10
DECLARE @BaseDate date = getdate()
DECLARE @ResultDate date
DECLARE @Query nvarchar(2000)

SET @Query = 'SELECT @result = DATEADD(@Interval, @Increment, CAST(@BaseDate AS DATE))'    

EXECUTE sp_executesql @Query,
           N'@result date OUTPUT, @Interval varchar(50), @Increment int, @BaseDate date',
           @Interval = @Interval, @Increment = @Increment, 
           @BaseDate = @BaseDate, @result = @ResultDate OUTPUT

SELECT @ResultDate

I've changed the SET @Query line to this one.

SET @Query = 'SELECT @result = DATEADD(' + @Interval +', @Increment, CAST(@BaseDate AS DATE))'

Although it works fine, I'm curious about why the first statement is causing the error in my dynamic SQL query?. Doesn't sp_executesql generate the same statement than the concatenated query one?

Xedni · Accepted Answer · 2017-09-15 21:52:28Z

2

So the way to think about parameterized dynamic sql is you can only use a parameter where you could if it were static SQL. DATEADD expects a special date part keyword (e.g. day, hour, year, etc), not a literal string, and not a variable. It's the same issue some people run into where they think they can parameterize something like a table name. The first statement fails because even in static sql, this is invalid:

declare @increment nvarchar(5) = 'day'

select dateadd(@increment, 1, getdate())

That's equivalent to

select dateadd('day', 1, getdate())

The second statement succeeds because you're concatenating the string "day" which gets evaluated to the keyword.

answered Sep 15, 2017 at 21:52

Xedni

4,7913 gold badges22 silver badges38 bronze badges

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

1 Comment

Kevin Suchlicki Over a year ago

This is a good answer, but to add something... see learn.microsoft.com/en-us/sql/t-sql/functions/… where it says about the datepart argument "User-defined variable equivalents are not valid."

Peter B · Accepted Answer · 2017-09-15 22:00:29Z

In the first case, the query (with @Interval expanded to its value) becomes this:

SELECT @result=DATEADD('DAY', @Increment, CAST(@BaseDate AS DATE))

and in the second query it becomes this:

SELECT @result=DATEADD(DAY, @Increment, CAST(@BaseDate AS DATE))

The first query is invalid because there the first parameter to DATEADD is a string value, where the compiler expects a language keyword, and those are not the same in SQL.

For more info, see here: https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql
Please note the line under datepart saying User-defined variable equivalents are not valid. In other words you can't put quotes around these "values", they are not strings but keywords, and they can not be put in variables.

Collectives™ on Stack Overflow

Issue with dynamic query and datepart SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related