An expression of non-boolean type specified in a context where a condition is expected, near ‘AND’

Question

ALTER PROCEDURE [dbo].[usp_BaseScan_CycleCount_Report]
@StartDate varchar(10),
@EndDate varchar(10)

AS

BEGIN

    IF @StartDate AND @EndDate IS NOT NULL
        SELECT * 
        FROM dbo.CycleCountHistory
        WHERE RecordDate BETWEEN @StartDate AND @EndDate
        
    ELSE 
        SELECT *
        FROM dbo.CycleCountHistory
END

Error message: Msg 4145, Level 15, State 1, Procedure
usp_BaseScan_CycleCount_Report, Line 16 An expression of non-boolean
type specified in a context where a condition is expected, near ‘AND’.
Msg 156, Level 15, State 1, Procedure usp_BaseScan_CycleCount_Report,
Line 21 Incorrect syntax near the keyword ‘ELSE’.

asked 2021-06-08 by pdart237

---

Answer

The problem is on this line:

IF @StartDate AND @EndDate IS NOT NULL

You’re expecting this to be evaluated as:

IF (@StartDate AND @EndDate) IS NOT NULL

But the way SQL Server evaluates it is:

IF (@StartDate) AND (@EndDate IS NOT NULL)

So the error is that @StartDate is not a boolean expression, and cannot be used as part of the IF expression.

Instead, you’ll need to do something like:

IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL

answered 2021-06-08 by Andy Mallon