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