May 14, 2015 at 8:12 pm
I am having an issue with dynamic sql.
I am getting this error:
Msg 402, Level 16, State 1, Line 27
The data types varchar and date are incompatible in the add operator.
If I run the query normally, it works fine. But if I try to put it in a variable but splitting out the variable, I get the error.
This part works fine.
DECLARE @DateRange INT
DECLARE @sql VARCHAR(2000),
@EndDate Date
SELECT @DateRange = 12
SELECT @EndDate = DATEADD(MONTH,-@DateRange,GETDATE())
CREATE TABLE #Temp
(
DocDate DATETIME
)
INSERT #Temp (DocDate) VALUES (GETDATE())
SELECT *
FROM #Temp
WHERE (CAST(DocDate AS DATE) BETWEEN CAST(@EndDate AS DATE) AND CAST(GetDate()AS DATE))
But if I just put the SELECT statement into the following string, it gets the error:
SELECT @sql = 'SELECT *
FROM #Temp
WHERE (CAST(DocDate) BETWEEN CAST(' + @EndDate + ' AS DATE) AND CAST(GetDate()AS DATE))'
I also tried to move the quotes around the CAST statement and got the same error:
SELECT @sql = 'SELECT *
FROM #Temp
WHERE (CAST(DocDate) BETWEEN ' + CAST(@EndDate AS DATE) + ' AND CAST(GetDate()AS DATE))'
How do I change the statement to fix this?
Thanks,
Tom
May 14, 2015 at 8:38 pm
CAST(@EndDate AS VARCHAR(10))
May 14, 2015 at 10:07 pm
I don't understand.
Why does it work without casting it to varchar in the first version?
Thanks,
Tom
May 14, 2015 at 10:35 pm
tshad (5/14/2015)
I don't understand.Why does it work without casting it to varchar in the first version?
Thanks,
Tom
You are missing the data type in the first cast in the where clause!
😎
My question is why on earth would you ever want to do it this way? From what I can see here, there are no benefits from the dynamic sql, no index can satisfy the query, it is wide open to injection, the adhoc plan will not be cached etc. etc.
May 14, 2015 at 10:45 pm
The reason it doesn't work in either of your original versions is because they are both attempting to concatenate a date datatype to a varchar data type... The two data types are incompatible. Casting the date to varchar allows the concatenation to work.
May 15, 2015 at 10:21 am
The reason I am doing it this way is that this select is really part of an OpenQuery statement. And if you use parameters, apparently you have to put the whole statement into a string and Exec it.
May 15, 2015 at 10:23 am
That makes sense.
Would it be better to surround the @Enddate in the first example with a Convert to Varchar or the second one (the cast statement)?
Thanks,
Tom
May 15, 2015 at 10:38 am
It depends... CAST is typically the preferred syntax being that it's an ansi standard... But, sometimes it's necessary, when working with different date/time data types to use the CONVERT function in order to use the optional style parameter.
Basic rule of thumb... If CAST will work, use CAST... When it won't, use CONVERT and set the appropriate style value.
May 15, 2015 at 10:51 am
Never use a function on a column if it can be avoided.
Never use between for date/datetimes, use >= and < instead.
SELECT @sql = 'SELECT *
FROM #Temp
WHERE DocDate >= ''' + CONVERT(char(8), @EndDate, 112) + ''' AND ' +
'DocDate < ''' + CONVERT(char(8), DATEADD(DAY, 1, GETDATE()), 112) + ''''
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 15, 2015 at 11:25 am
Or you could prevent all that casting by parametrizing your query.
SELECT @sql = 'SELECT *
FROM #Temp
WHERE DocDate BETWEEN @EndDate AND GetDate()'
EXEC sp_executesql @sql, N'@EndDate date', @EndDate
May 15, 2015 at 1:07 pm
Luis Cazares (5/15/2015)
Or you could prevent all that casting by parametrizing your query.
SELECT @sql = 'SELECT *
FROM #Temp
WHERE DocDate BETWEEN @EndDate AND GetDate()'
EXEC sp_executesql @sql, N'@EndDate date', @EndDate
True, if you want to execute the query directly, but then there is no reason to use dynamic sql for that query. Now, if you are going to use this in an OPENQUERY (which is what the OP is doing), you can't as variables aren't allowed in the query.
May 15, 2015 at 1:10 pm
If an OPENQUERY statement could make use of the @EndDate parameter, he wouldn't need dynamic SQL in the first place.
I have a lot of code that references our non-SQL accounting system, and the drivers require OPENQUERY statements instead of four-part names. So I run into this issue all the time. Some of the code I inherited builds dynamic SQL with so many concatenated expressions that it is hard to see what the statement is doing.
I prefer to use a template statement with replacement tags for all the parameters, then surround it with a REPLACE() function for each tag.
SET @cmd = REPLACE(REPLACE(REPLACE(
'SELECT * FROM OPENQUERY(Link, ''SELECT * FROM tbl
WHERE intcol=<intval> AND strcol="<strval>" AND dtcol > <dateval>'')',
'<intval>', @Int),
'<strval>', @STR),
'<dateval>', CONVERT(CHAR(10), @Date, 101));
This would be formatted for the system I deal with, which uses double-quote delimiters for string literals and accepts date literals with no delimiters but only in the format MM/DD/YYYY.
I find this to be a lot more readable, especially if some tags are used more than once in the template. It's much easier to verify that the basic statement has the right number of delimiters in all the right places. The replacement values can be expressions, and the REPLACE function will do an implicit CAST to VARCHAR of things like integers. I'm now dealing with another level of linked servers so I have nested OPENQUERYs, with plenty of doubled and quadrupled quotes. If I had to add parameters using "...''''' + CAST(@Int AS VARCHAR) + '''''..." I would go nuts counting the stupid quotes. And fighting with Intellisense when it decides to add extras.
May 16, 2015 at 2:13 am
Scott Coleman (5/15/2015)
If an OPENQUERY statement could make use of the @EndDate parameter, he wouldn't need dynamic SQL in the first place.I have a lot of code that references our non-SQL accounting system, and the drivers require OPENQUERY statements instead of four-part names. So I run into this issue all the time. Some of the code I inherited builds dynamic SQL with so many concatenated expressions that it is hard to see what the statement is doing.
I prefer to use a template statement with replacement tags for all the parameters, then surround it with a REPLACE() function for each tag.
SET @cmd = REPLACE(REPLACE(REPLACE('SELECT * FROM OPENQUERY(Link, ''SELECT * FROM tbl
WHERE intcol=<intval> AND strcol="<strval>" AND dtcol > <dateval>'')',
'<intval>', @Int),
'<strval>', @STR),
'<dateval>', CONVERT(CHAR(10), @Date, 101));
This would be formatted for the system I deal with, which uses double-quote delimiters for string literals and accepts date literals with no delimiters but only in the format MM/DD/YYYY.
I find this to be a lot more readable, especially if some tags are used more than once in the template. It's much easier to verify that the basic statement has the right number of delimiters in all the right places. The replacement values can be expressions, and the REPLACE function will do an implicit CAST to VARCHAR of things like integers. I'm now dealing with another level of linked servers so I have nested OPENQUERYs, with plenty of doubled and quadrupled quotes. If I had to add parameters using "...''''' + CAST(@Int AS VARCHAR) + '''''..." I would go nuts counting the stupid quotes. And fighting with Intellisense when it decides to add extras.
+1
😎
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply