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

June 8, 2021 Andy DBA Stack Exchange 0

clip art of a double-quote character

Question

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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:

1
IF @StartDate AND @EndDate IS NOT NULL

You’re expecting this to be evaluated as:

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

But the way SQL Server evaluates it is:

1
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:

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

answered 2021-06-08 by Andy Mallon