ansaurus

Question

Answer 1

+12 A:

Replace "execute" with "print", drop the parenthesis, and run, and you get

delete from HttpRequests where Date < '2009-08-0'

That is not a valid date.

Philip Kelley 2009-11-17 19:30:10

Good point, but does that explain the compile error?

John M Gant 2009-11-17 19:57:50

When syntax errors are involved, the actual errors thrown by SQL tend to be somewhat... arbitrary.

Philip Kelley 2009-11-17 21:08:42

No, the problem is that you can't perform operations like that within EXEC (just like you can't call EXEC dbo.someProc @foo = (@bar + 1);). Use a variable as a placeholder once you get proper date formatting in place, then use EXEC(@variable);.

Aaron Bertrand 2009-11-17 21:19:58

Answer 2

A:

Query dates like yyyyMMdd and put max between [].

Michel van Engelen 2009-11-17 19:32:09

SQL Server can understand dates in ISO 8601 format ("yyyy-MM-dd") and `nvarchar(max)` is legal syntax for SQL Server.

Daniel Pryden 2009-11-17 19:43:52

That can be, but is it clear what is meant? Max can misinterpreted as a reserved word. Date like yyyyMMdd is more used than dates containing '-' or '/'. I really wouldn't want to read your code Daniel, even when it works..

Michel van Engelen 2009-11-17 19:55:17

Daniel, YYYYMMDD is much safer. Try this: SET LANGUAGE FRENCH; SELECT CONVERT(DATETIME, '2009-11-13');

Aaron Bertrand 2009-11-17 21:16:52

I'm sorry, are you saying that ISO 8601 is ambiguous? It's an international standard, after all. The YYYYMMDD form is called *basic format* by the standard, while the YYYY-MM-DD form is called *extended format*. See: http://en.wikipedia.org/wiki/ISO_8601#Dates . I wasn't trying to say that YYYYMMDD is a bad idea (I think it's a great idea, by the way), but that YYYY-MM-DD *ought* to work, and thus isn't the problem here.

Daniel Pryden 2009-11-17 23:55:49

@Michel: You say: "Max can be misinterpreted as a reserved word." This is true. But the syntax `nvarchar(max)` is well documented, and there is no alternative syntax for specifying that you want a string with up to 2^31-1 bytes of storage (if you specify a numeric value, the maximum is 4000 bytes). So there is nothing wrong with using `nvarchar(max)`.

Daniel Pryden 2009-11-18 00:01:15

Daniel, I'm not suggesting that YYYY-MM-DD is the problem here. I'm suggesting that in general it is not a good choice in SQL Server because it CAN be a problem in other instances. Did you try the sample I posted?

Aaron Bertrand 2009-11-19 17:42:23

Answer 3

+1 A:

You don't seem to be creating a valid date.

select 'delete from HttpRequests where Date < ''2009-08-' + convert(nvarchar(max), 0)

+ ''''

would give you: delete from HttpRequests where Date < '2009-08-0'

Jeff O 2009-11-17 19:36:58

Answer 4

+2 A:

There is nothing wrong with the statement. SQL simply doesn't allow passing result of CONVERT expression to EXEC. I can't find any documentation for this, but many people report this behavior.

http://www.eggheadcafe.com/software/aspnet/34917755/pass-result-of-convert-as.aspx

LymanZerga 2009-11-17 19:43:38

`execute` does not create a stored procedure, so your comment does not apply. Reference: http://msdn.microsoft.com/en-us/library/ms188332.aspx

Daniel Pryden 2009-11-17 19:45:02

Thanks, I know that. My point was regarding passing parameters to EXEC.

LymanZerga 2009-11-17 19:47:42

@LymanZerga: My mistake. I wasn't aware of this behavior.

Daniel Pryden 2009-11-17 23:48:05

Answer 5

+3 A:

EXECUTE (@stringvariable) does not allow concatenation more complex than @stringvariable1 + @stringvariable2

You'd have to do this:

DECLARE @sql nvarchar(4000)
SELECT @sql = 'delete from HttpRequests where Date < ''2009-08-' + convert(nvarchar(4000), 0) + '''
EXECUTE (@sql)

However, then you have the invalid date error... and I've fixed the quote error too...

gbn 2009-12-27 13:22:41

ansaurus

tags:

views:

answers:

What is wrong with this SQL?

related questions