CASE with NULLs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CASE with NULLs

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2008-08-13 : 22:13:01

In a stored procedure, there's a SELECT with a couple of WHERE criteria determined by input parameters. One of the parameters is @BillNo, and it can be null. The column it searches can contain nulls as well. HOWEVER, if the @BillNo parameter is not null, it should filter the column. The code is currently not working properly because even if the @BillNo param is null, C.BillNo will still not compare to itself if it is null, since a null never matches a null.

SELECT DISTINCT OrderNo FROM Customers C INNER JOIN Orders O INNER JOIN Orderdetail OD ON OD.Voucher = O.VoucherNo ON C.ID = O.Custid WHERE C.Account BETWEEN @StartAcct AND @EndAcct AND C.IsBillable = 1 AND C.BillNo = (CASE WHEN @BillNo IS NOT NULL THEN @BillNo ELSE C.BillNo END) AND OD.OrderDate BETWEEN @Start  AND @End AND (O.Printed = @NPrinted OR O.Printed = @YPrinted) AND OD.SingleBalance > 0

Sarah Berger MCSD

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-08-14 : 00:50:33

try this


SELECT DISTINCT OrderNo 
FROM Customers C 
INNER JOIN Orders O ON C.ID = O.Custid 
INNER JOIN Orderdetail OD ON OD.Voucher = O.VoucherNo 
WHERE C.Account BETWEEN @StartAcct AND @EndAcct 
		AND C.IsBillable = 1 
		AND (@BillNo IS NULL OR C.BillNo = @BillNo) 
		AND OD.OrderDate BETWEEN @Start  AND @End 
		AND (O.Printed = @NPrinted OR O.Printed = @YPrinted) 
		AND OD.SingleBalance > 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 02:40:46

or use COALESCE

SELECT DISTINCT OrderNo 
FROM Customers C 
INNER JOIN Orders O ON C.ID = O.Custid 
INNER JOIN Orderdetail OD ON OD.Voucher = O.VoucherNo 
WHERE C.Account BETWEEN @StartAcct AND @EndAcct 
		AND C.IsBillable = 1 
		AND C.BillNo = COALESCE(@BillNo,C.BillNo ) 
		AND OD.OrderDate BETWEEN @Start  AND @End 
		AND (O.Printed = @NPrinted OR O.Printed = @YPrinted) 
		AND OD.SingleBalance > 0

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-08-14 : 03:21:29

when using COALESCE put like this

COALESCE(C.BillNo, '') = COALESCE(@BillNo,C.BillNo, '')

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2008-08-14 : 11:38:56

The solution was to use

WHERE ((@BillNo IS NULL AND C.BillNo IS NULL) OR C.BillNo = @BillNo)

to return all NULL rows if the parameter is null, or only the relevant non-null rows if the parameter has a value.

PeterNeo, COALESCE on the field, which would throw off the optimizer and the code does the same thing as the code in your previous post which is more efficient, but wouldn't do the job as it would return all NULL and/or matching rows regardless of whether @BillNo was NULL or had a value.

Visa - if C.BillNo is NULL, and @BillNo is NULL, it would still return nothing since COALESCE wouldn't find a non-null value to match to. And null never equals null so the column value wouldn't equal itself in a comparison.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources