CODEWITHSUNDEEP
sql-serverdatabaset-sqlstored-procedures
Burak Benli
Burak Benli

Reputation: 39

SQL Server Incorrect syntax near '11'

I get the error Incorrect syntax near '11'. in SQL Server.

There is a stored procedure SP_TEST:

...
@ALISTARIHI datetime
...
AS
BEGIN
...
exec('CONVERT(DATETIME,'+@ALISTARIHI+')')

Calling SP_TEST:

exec SP_TEST '08.11.2013'

As I mentioned above, I get error

Incorrect syntax near '11'

when I call. How can I fix it?

NOTE: Some of the above code looks

Upvotes: 0

Views: 4289

Answers (2)

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

The problem is that you pass 08.11.2013 as parameter and after concatenating in EXEC you will receive CONVERT(DATETIME,08.11.2013) , i.e. you will not have string - this is the cause for ERROR. It must be CONVERT(DATETIME,'08.11.2013')

The command exec('CONVERT(DATETIME,'+@ALISTARIHI+')') also does not make any sense to me. It is wrong. Must be exec('SELECT CONVERT(DATETIME,'''+@ALISTARIHI+''')'). And the result will be outside of the scope of the current code (because of the EXEC).

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

So many things wrong.

  1. The comment about converting a datetime to a datetime is spot on. Why are you doing this? And why is your datetime in an ambiguous format? I have absolutely no idea if that is supposed to be August 11th or November 8th. Try using yyyymmdd.

  2. Assuming you do need to do this, why are you doing it in dynamic SQL?

  3. Assuming you do need dynamic SQL, why is your statement incomplete? What does this do, even if you put string delimiters around the value?

    CONVERT(DATETIME, '20131108');

    Oh, that's right:

    Msg 156, Level 15, State 1
    Incorrect syntax near the keyword 'CONVERT'.

    The statement isn't even complete. So maybe you meant:

    EXEC('SELECT CONVERT(DATETIME,'''+@ALISTARIHI+''')')

But in reality, you probably want something like this instead:

DECLARE @sql NVARCHAR(MAX) = N'SELECT CONVERT(DATETIME, @d);';
EXEC sp_executesql @sql, N'@d DATETIME', @ALISTARIHI;

But I still don't understand why you're converting a datetime to a datetime.

P.S. Why would you name your variable something so long and difficult to type?

Upvotes: 5

Related Questions