Incorrect syntax near the keyword 'CONVERT' (In stored procedure EXEC)

Question

I am running my stored procedure that takes only one argument of type DATE. The name of the argument is DateParam

EXEC [MyDBNAME].[dbo].[SPName] @DateParam = CONVERT(DATE, GETDATE());

But I get the following error:

Incorrect syntax near the keyword 'CONVERT'.

I have seen this error in many other questions here as well as other forums. But I did not find one that answers the problem. Thanks for looking!

You can't use expressions here. Assign the value to a parameter. — Gordon Linoff, Aug 11 '20 at 14:50

Gordon Linoff · Accepted Answer · 2020-08-11T14:58:36.407

4

SQL Server doesn't parse expressions in exec. So assign to a parameter:

DECLARE @today DATE = CONVERT(DATE, GETDATE());

EXEC [MyDBNAME].[dbo].[SPName] @DateParam = @today;

I imagine that Microsoft has a good reason for this limitation -- such as parsing ambiguities in the line. It is a pain.

edited Aug 11 '20 at 14:58

answered Aug 11 '20 at 14:50

Gordon Linoff

Brilliant! Thanks! But we should add "AS DATE" in the `Declare` line. `DELCARE @today AS DATE`. – disasterkid Aug 11 '20 at 14:55
Will accept as answer in 5 mins ;) – disasterkid Aug 11 '20 at 14:58

1 Answers1