Microsoft SQL CASE statement in where clause using LIKE

Question

Hi,

I once used a scenario where I did a SQL where clause like this: where BranchName = CASE WHEN @pBranch <> '' then @pBranch else BranchName end

this is to say we're doing a branch name lookup, but this statement has to cater for the LIKE clause somehow. We need to be able to say 'If the pBranch parameter was provided then do a like search else ignore that part of the WHERE clause.

Any clues?

Thanks, Jacques

Answer 1

try

Where BranchName Like IsNull(@Branch, BranchName)

Answer 2

you could say:

SELECT *
FROM dbo
WHERE @pBranch = '' OR BranchName = @pBranch

This basically says that if you pass in '' to @pBranch, then all results will be displayed, otherwise, if you pass in something else, it will search by your argument

Answer 3

actually I think the answer I was looking for is as follows:

... where o.BranchName like case when @pBranchName <> '' then @pBranchName else o.BranchName end ...

Answer 4

where @pBranch = '' or BranchName like @pBranch

(add the wildcards to the appropriate spot in the like clause)

this will work for numbers, not just strings - so if you passed in an int parameter as a 0 or a value, you would do:

where @param = 0 or DBField = @param