Weigh Resultset

Hi,

This is what I want to do. For a data acquisition query, if condition
A is met, set weight to 1, else if condition B is met, set weight to 2
etc.
Is this possible? I thought about using CASE function but to no
avail.

Using the classical Northwind db as target db, and its employees table
as target table, if [note] column contains 'BA', then I want to set
weight to 1, else if it contains 'BTS', then I want to set weight to 2
etc. Weight or the like is an auxillary artificially created column.

Something like

DECLARE @col int

select firstname, lastname, @col =
(
CASE notes
when '%BA%' then 1
when '%BTS%' then 2
else 0
END as aiCOL
)
-- and I'd like to alias @col as aiCOL or whatever
from employees
where notes LIKE
'%BA%'
OR
notes LIKE '%BTS%'

failed.

Thanks.