Conditional Where wildcard problem

Hi,

I have a problem using the LIKE operator in a stored procedure. I have simplified the script so that it runs in query analyser and still have the same problem. The script is:

DECLARE @.FirstName varchar (50)

SELECT @.FirstName = 'B%'

SELECT * FROM PhoneList
WHERE PhoneList.FirstName LIKE CASE @.FirstName WHEN '' THEN PhoneList.FirstName ELSE @.FirstName END

This code produces no rows in the result. However if I change the second line to:
SELECT @.FirstName = 'Ben'
Then I get all of the rows with 'Ben' as the first name. If I change it to:
SELECT @.FirstName = 'Be%'
Then I get all of the rows with three character first names beginning with 'Be'. If I change it to:
SELECT @.FirstName = 'B%%'
Then I get all of the three character first names beginning with 'B'.

I need the conditional where so that if an empty string is passed it returns every row, which works fine as it is.

The % wildcard appears to be operating the same way as the _ wildcard. Has anyone seen this before?

This is SQL Server 2k SP3 on Win2003 server.

thanks
BenHi,

maybe you could try this:

DECLARE @.FirstName varchar (50)

SELECT @.FirstName = 'B%'

SELECT * FROM PhoneList
WHERE PhoneList.FirstName LIKE @.FirstName + '%'

If @.FIrstName is an empty string the statement should return all data.

;)