0

I have a stored proc which looks like this:

CREATE PROC [dbo].[UpdateLastCleanDatesNormalSchedule] 
    @BaseDate DATETIME = NULL
AS
IF @BaseDate IS NULL
SET @BaseDate=GETDATE()

UPDATE Locations
SET LastCleaned=dbo.fnFindTheLastDay(vw.CleanDay,@BaseDate,LastCleaned)
FROM Locations
INNER JOIN vwMostRecentCleanDay vw ON 
Locations.NormalFrequencyCode=vw.FrequencyCode

The code is pretty simple, as you can see, calling a function to update data for a street-cleaning database with the date when the location was last cleaned.

Here's my problem. This works:

EXEC dbo.UpdateLastCleanDatesNormalSchedule

But this doesn't:

EXEC dbo.UpdateLastCleanDatesNormalSchedule GETDATE()

and nor does this:

EXEC dbo.UpdateLastCleanDatesNormalSchedule @BaseDate=GETDATE()

Either of these result in the error

Incorrect syntax near ')'.

and if I hover over the wiggly red line, by the closing bracket of the call to GETDATE(), the tooltip reports " Incorrect syntax near ')'. Expecting SELECT or '('.

If I want to pass in a specific date and try this:

EXEC dbo.UpdateLastCleanDatesNormalSchedule CONVERT(Datetime,'01 September 2016')

I get the error

Incorrect syntax near the keyword 'CONVERT'.

But if I pass in the date as a pure string:

EXEC dbo.UpdateLastCleanDatesNormalSchedule '01 September 2016'

then it works.

In desperate hope that this was some kind of glitch rather than an error on my part, I tried dropping and recreating the proc, but no luck.

I'm very confused. Can anyone help?

Andrew Richards
  • 321
  • 1
  • 9
  • Since you already use `GETDATE()` when the parameter value is NULL, why not just pass NULL into your stored procedure to get it to work? – Bridge Oct 20 '16 at 14:30

1 Answers1

1
Declare @MyDate Datetime = getdate()
EXEC dbo.UpdateLastCleanDatesNormalSchedule @MyDate
Neo
  • 3,309
  • 7
  • 35
  • 44