user1688401 user1688401 - 4 months ago 43
SQL Question

Stored Procedure Operand type clash: date is incompatible with int

This is my stored procedure

ALTER PROCEDURE [dbo].[sp_ListFirmJobFilter]
(@Status as nvarchar(10),
@CityID as nvarchar(10),
@DataStart as date,
@DataEnd as date,
@FirmID as nvarchar(10)
)
AS
BEGIN
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'SELECT * FROM Jobs WHERE 1=1'

IF @Status IS NOT NULL
BEGIN
SET @Sql += ' AND Status = '+@Status+''
END

IF @CityID IS NOT NULL
BEGIN
SET @Sql += ' AND CityID = '+@CityID+''
END

IF @FirmID IS NOT NULL
BEGIN
SET @Sql += ' AND FirmID = '+@FirmID+' '
END

IF @DataStart IS NOT NULL
BEGIN
SET @Sql += ' AND (InsertedDate >= ' + CONVERT(VARCHAR(50), @DataStart)
SET @Sql += ' AND InsertedDate <= ' + CONVERT(VARCHAR(50), @DataEnd)
SET @Sql += ')'
END

PRINT(@Sql)
EXEC(@Sql)
END


The command completed successfully.

But when I execute that stored procedure with that parameter

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_ListFirmJobFilter]
@Status = NULL,
@CityID = null,
@DataStart = N'2016-06-26',
@DataEnd = N'2016-06-28',
@FirmID = 4

SELECT 'Return Value' = @return_value
GO


I get an error:


Operand type clash: date is incompatible with int


I look for executed SQL command (Print SQL string). This is executed command:

SELECT *
FROM Jobs
WHERE 1=1
AND FirmID = 4
AND (InsertedDate >= 2016-06-26 AND InsertedDate <= 2016-06-28)


When I execute that it give same error. I changed it to:

SELECT *
FROM Jobs
WHERE 1=1
AND FirmID = 4
AND (InsertedDate >= '2016-06-26' AND InsertedDate <= '2016-06-28')


this worked.

What should I do?
InsertedDate
type is date

Answer

Wrap dates in single quotes

SET @Sql += ' AND (InsertedDate >= ''' + CONVERT(VARCHAR(50), @DataStart) +''''
SET @Sql += ' AND InsertedDate <= ''' + CONVERT(VARCHAR(50), @DataEnd) +''''
SET @Sql += ')'