Getting error "Incorrect syntax near '-' " while executing Stored Procedure

Question

I have successfully created a stored procedure using two input parameters and two output parameters. when I executed this procedure, I'm getting an error

Incorrect syntax near '-'

near the first parameter.

ALTER PROCEDURE [dbo].[Agent_interactions_report]
(
@StartDate date, 
@EndDate date,
@ERRORCODE AS INT OUTPUT,
@ERRORDESCRIPTION AS VARCHAR(4000) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;

    begin Try

    select 
        cast([CallStartTime] as Date) as Date, 
        [AgentID] as [Agent ID], [Agent_Name] as [Agent name], 
        [CustomerID] as [Cust-ID], [Account_Number] as [Account number],
        [Transaction_Des] as [Transaction], 
        CallStartTime AS [Call start time], 
        CallEndTime AS [Call end time], CallID as [Call ID]
    from  
        [TBL_AGENT_TRANSACTIONS]
    where 
        cast(CallStartTime as DATE) >= @StartDate 
        and cast(CallEndTime as Date) <= @EndDate
end Try
begin catch
        SET @ERRORCODE = ERROR_NUMBER() 
        SET @ERRORDESCRIPTION = ERROR_MESSAGE() 
end catch

END

This is the executed result:

DECLARE @return_value int,
        @ERRORCODE int,
        @ERRORDESCRIPTION varchar(4000)

EXEC    @return_value = [dbo].[Agent_interactions_report]
        @StartDate = 2015-04-27,
        @EndDate = 2015-04-27,
        @ERRORCODE = @ERRORCODE OUTPUT,
        @ERRORDESCRIPTION = @ERRORDESCRIPTION OUTPUT

SELECT  @ERRORCODE as N'@ERRORCODE',
        @ERRORDESCRIPTION as N'@ERRORDESCRIPTION'

I'm getting error near "@StartDate = 2015-04-27," but when I executed the SP manually by giving this inputs to the input parameters i'm getting the expected result.

Note:

Since my reputation is below 10 i'm not supposed to upload screenshot which will be more useful to understand the issue. Please let me know if you need more info.

It thinks your startdate and enddate are strings and therefore needing ' around them. — Allan S. Hansen
@Allan Yes you are correct but my doubt is how it will be string? In my Proc I have declared startdate and enddate with datatype 'date' only? — User
'2015-04-27' is a string which will be converted to date when passed to a date variable. Another option is to use DECLARE @dt DATE = DATEFROMPARTS(2015,04,27) and then use @dt in your exec like @StartDate =@dt — ughai

David Hoelzer David Hoelzer · Accepted Answer · 2015-05-06T11:44:59

When handling dates in this way you will need to encapsulate them in quotation marks! It's failing to identify the type of data that you have and believes that you are sending a string.

Getting error "Incorrect syntax near '-' " while executing Stored Procedure

2 Answers