CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlstored-procedures
User2012384
User2012384

Reputation: 4927

SQL "Incorrect syntax near '1900-01-01 00:00:00'." when trying to execute a stored procedure

I have a stored procedure like below:

ALTER procedure [dbo].[test_stored_procedure]

@id                         nvarchar(20),
@datefm                     datetime,
@dateto                     datetime

as
   ...
end

Then I'm trying to call the above stored procedure using below SQL:

exec test_stored_procedure @searchKey, cast('1900-01-01 00:00:00' as datetime), cast('1900-01-01 00:00:00' as datetime)

But when executed, I got the below error:

Incorrect syntax near '1900-01-01 00:00:00'.

How to fix this?

Upvotes: 1

Views: 2471

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

You need use:

DECLARE
   @date_start DATETIME = '1900-01-01 00:00:00'
   ,@date_stop DATETIME = '1900-01-01 00:00:00';

EXEC test_stored_procedure @searchKey, @date_start, @date_stop;

Or: 

EXEC test_stored_procedure @searchKey, '1900-01-01 00:00:00', '1901-01-01 00:00:00';

It is because you cannot use function on parameters with EXEC See doc. You can pass only:

  1. value
  2. variable
  3. DEFAULT

[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ]

Upvotes: 6

Related Questions