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.
Last edited by Ben_Smith; 01-14-05 at 06:34.
Reason: extra platform info