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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , ,

Leave a Reply