In this article let us go over the scenarios which causes an error like the below one and how to solve it.

Msg 102, Level 15, State 1,
Line 2 Incorrect syntax near ‘)’.’

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 two 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

RESULT:

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

RESULT:

Example 2:

Try executing the above created user defined function fnGetYearPart with GETDATE() as parameter:

DECLARE @RetValue INT
EXEC @RetValue = dbo.fnGetYearPart GETDATE()
SELECT @RetValue 'Function Return Value'
GO

RESULT:

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 parameter as shown in the below script:

DECLARE @RetValue INT
DECLARE @Today DATETIME
SET @Today = GETDATE()
EXEC @RetValue = dbo.fnGetYearPart @Today
SELECT @RetValue 'Function Return Value'
GO

RESULT: