CASE statement and NULL / NOT NULL

[fiveeuros]

Hi,

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 'AFFILIATED' is passed as the parameter, only those records with an int value in brokeraffiliation.affiliatedcompanyid are returned;
- when 'NON-AFFILIATED' 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 = 'AFFILIATED' 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?

 

[bborissov]

and 
((@BrokerType = 'NON-AFFILIATED' AND brokeraffiliation.affiliatedcompanyid IS NULL) OR
(@BrokerType = 'AFFILIATED' AND brokeraffiliation.affiliatedcompanyid IS NOT NULL) OR
RIGHT(@BrokerType,10) <> 'AFFILIATED')

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.