EXECUTE/EXEC Stored Procedure/Function Statement may raise an error like: ‘Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ‘)’.’ if GetDate() like function is used in it – Sql Server

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:
Incorrect syntax near ) err

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:
Incorrect syntax near ) Solution

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:
Incorrect syntax near ) Error

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:
Incorrect syntax near ) Error Solution

One thought on “EXECUTE/EXEC Stored Procedure/Function Statement may raise an error like: ‘Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ‘)’.’ if GetDate() like function is used in it – Sql Server

  1. It’s always a good idea to assign value of GETDATE() to a local variable and use it, so that everywhere it’ll be consistent…

Leave a Reply

Your email address will not be published. Required fields are marked *