Conditional AND statement in stored procedure Select query?

This should be simple but I can't figure it out. So I have a select query an
d
I want to run a conditional AND if a value is not NULL. Here is the type of
statment I want to run.
-- Declared values not shown.
SELECT * FROM MyTABLE
WHERE city = @.city
AND department = @.department
IF @.statusCode is NOT NULL
AND status IN (SELECT Item FROM TsqlSplit(@.statusCode) TsqlSplit)
END
-- The TsqlSplit function simply takes in a string of statusCodes "1,4,6"
and splits them out for the IN clause. It works fine if a string is actually
passed but I simply want to execute the IN clause if the @.statusCode value
passed to the procedure is not NULL.
I looked at the WHEN THEN clause but that only seems to work for equalities
AND status = WHEN @.statusCode IS NULL THEN ...
Thanks folks.SQL will short-circuit when the first part of an OR condition fails, so
this should work (performance may suffer though)
...
and (@.statusCode is null
or status in (select item from dbo.tsqlSplit(@.statusCode))
another option is to default the @.statusCode variable to the list of
possible status codes. then you won't have to check if @.statusCode is null.
Ramez wrote:
> This should be simple but I can't figure it out. So I have a select query
and
> I want to run a conditional AND if a value is not NULL. Here is the type o
f
> statment I want to run.
> -- Declared values not shown.
> SELECT * FROM MyTABLE
> WHERE city = @.city
> AND department = @.department
> IF @.statusCode is NOT NULL
> AND status IN (SELECT Item FROM TsqlSplit(@.statusCode) TsqlSplit)
> END
> -- The TsqlSplit function simply takes in a string of statusCodes "1,4,6"
> and splits them out for the IN clause. It works fine if a string is actual
ly
> passed but I simply want to execute the IN clause if the @.statusCode value
> passed to the procedure is not NULL.
> I looked at the WHEN THEN clause but that only seems to work for equalitie
s
> AND status = WHEN @.statusCode IS NULL THEN ...
> Thanks folks.