CASE statement and NULL/NOT NULL

Question

Post reply

CASE statement and NULL/NOT NULL

Keith Wilson-307024

Old Hand

Points: 354
More actions
July 3, 2006 at 6:51 am

#91571

Hello,

I'm trying to work a stored procedure with a NULL value in a case statement and can't get the syntax correct.

I've got a varchar parameter (@BrokerType) that can be either 'AFFILIATED', 'NON-AFFILIATED' or 'BOTH'.

The stored procedure then has a join (simple 1-1 join using a WHERE clause) from a certificate table to a brokeraffiliation table which has an int field named affiliatedcompanyid.

What I'm trying to achieve is:
- when 'AFFILIATES' is passed as the parameter, only those records with an int value in brokeraffiliation.affiliatedcompanyid are returned;
- when 'NON-AFFILIATES' is passed as the parameter, only those records with a NULL value in brokeraffiliation.affiliatedcompanyid are returned;
- when 'BOTH' is passed as the parameter, all records are returned.

I've come to a bit of a mental block with:
...
and brokeraffiliation.affiliatedcompanyid =
case when @BrokerType = 'NON-AFFILIATED' then null
case when @BrokerType = 'AFFILIATES' then not null
else affiliation.affiliatedcompanyid
end

but this gives a syntax error at the 'not null' on the second case branch.

I'm guessing (hoping) that there's an easy way to do this and I've missed it... can you point me in the right direction?

Thanks...

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Instead of a CASE statement, try this in your where clause instead:

((@BrokerType = 'NON_AFFILIATED' and affiliation.affiliatedcompanyid is null)

or (@BrokerType = 'AFFILIATES' and affiliation.affiliatedcompanyid is not null)

or (@BrokerType = 'BOTH'))

HTH,

Lynn

Vinay Kirtane SSC Enthusiast Points: 143 More actions · Answer 2

the correct syntax for not null check is 'IS NOT NULL' and not ' = not null', hence the syntax error.

I would think the easiest approach would be to use function ISNUMERIC instead of NULL and NOT NULL so

...

and ISNUMERIC(brokeraffiliation.affiliatedcompanyid) =

case when @BrokerType = 'NON-AFFILIATED' then 0

case when @BrokerType = 'AFFILIATES' then 1

else BETWEEN 0 and 1

end

Isnumeric will return all non-affiliated records that have null/alpha in affiliatedcompanyid

Else

Isnumeric will return all affiliated records which have affiliatedcompanyid as int

else it will return all records.

One point to consider though.... This will have some impact on the performance of your query. If performance is not an issue then it will work. If performace is an issue, you may want to re-write the query by replacing join by union...

VK

Jens Süßmeyer SSC Enthusiast Points: 165 More actions · Answer 3

Remember that NUMERIC has some drawbacks like those one posted from here: http://www.aspfaq.com/show.asp?id=2390

HTH, Jens Suessmeyer.

---

http://www.sqlserver2005.de

---

Kenneth Gladden Old Hand Points: 303 More actions · Answer 4

So instead of answering his question you flame him on syntax?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

I have to agree with Ken, Joe. After reading your response

above plus several others, you are down right mean and unhelpful.

You may be a smart SQL Server Guru and a published author, but

you totally lack any compassion or sense of tact when dealing with

others who may not have as much experience as you.

Try being helpful and supportive of others and help guide them on

how to write better SQL code instead of flaming them and condemning

them for how they may be writing code.

Keith Wilson-307024 Old Hand Points: 354 More actions · Answer 6

To those that helped, many thanks.

To those that chose to flame... well, my status on this forum is 'Newbie'. That's exactly what I am as far as SQL is concerned.

>> you seem to want to keep writing procedural code << >> you are missing the entire SQL model and working with a procedural file system in your head <<

I'm an OO (Java / C#) coder by trade, stepping in to write some stored procedures for a project that's slipping. I'm learning as I go.

>> You might want to consider shorter encoding that are less subjec (sic) to typographical. <<

These values have already been decided for me and cannot be changed, as has the database schema (apologies if this terminology is incorrect btw), which has been inherited from a 3rd party source.

Now while I appreciate that it may be frustrating for a guru to see what he or she considers to be basic errors in a question, it is equally frustrating for a newbie to the language to be flamed and - I'm sorry to say this - patronised - for having the temerity to ask such a question.

In many years of using the web this is the first time I've felt the need to defend myself in this way... way to welcome a new member of the community.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Lynn Pettis

SSC Guru

Points: 442467

July 5, 2006 at 7:22 am

#647495

Well said Keith.

gleazenby SSC Eights! Points: 800 More actions · Answer 8

Welcome Keith,

Don't let Joe Celko's comments upset you. Some people are bound and determined to always speak down to everyone – they like to make themselves feel important. That is the only way they can inflate their ego - no one else will do it for them.

Hey Joe, now that you have learned so much about SQL, why don’t you take a Communications Class and learn how to be an EFFECTIVE communicator – you have a ways to go buddy.

I'll bet Joe is 5' 9" and 140 lbs. (Hitler complex).

By the way, Joe has ONLY contributed 299 posts out of the 289,958 we have. If he went away, we wouldn't even miss him.

Hang around Keith. Don't let BIG BAD Joe scare you away.