Msg 102, Level 15, State 1, Line 2 Incorrect syntax near in SQL Server 2014

Why we get this error in SQL SERVER.

And what does means of this Error

Basically, we will get the error like the above one if we use GETDATE() like function as a parameter to EXECUTE / EXEC Stored Procedure/Function Statement.

To demonstrate this error scenario, let us first create a Stored Procedure and a User defined function as below:

--Create Demo Stored Procedure

CREATE PROCEDURE dbo.SPGetYearPart(@Date AS DateTime)

AS

BEGIN

    RETURN YEAR(GETDATE())

END

GO

--Create Demo User Defined Function

CREATE FUNCTION dbo.fnGetYearPart(@Date AS DateTime)

RETURNS INT

AS

BEGIN

    RETURN YEAR(@Date)

END

GO

Below examples demonstrates when this error occurs and how to solve it:

Example 1:

Try executing the above created stored procedure SPGetYearPart with GETDATE() as parameter:

DECLARE @RetValue INT

EXEC @RetValue = dbo.SPGetYearPart GETDATE()

SELECT @RetValue 'SP Return Value'

GO

Solution:

Solution to this problem is: instead of passing GETDATE() function as parameter directly in the execute statement, assign it to a local variable and pass the assigned local variable as the parameter as shown in the below script:

DECLARE @RetValue INT

DECLARE @Today DATETIME

SET @Today = GETDATE()

EXEC @RetValue = dbo.SPGetYearPart @Today

SELECT @RetValue 'SP Return Value'

GO

Hope you can understand.