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

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'.

Asked2021-06-08bypdart237

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

Answered2021-06-08byAndy Mallon