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.

;)

Concatenating columns with datatype ntext

How can I concatenate 2 columns, both of type ntext, in a select statement?
I get the following error.
"Invalid operator for datatype. Operator equals add, type equals ntext"You cannot use concatenation on TEXT datatype. If the length of the value is
less than 8000, you can use the CAST function to change it to VARCHAR
datatype and do the concatenation. Otherwise, you'll have to return them as
distinct values to the client and leverage the client's string concatenation
capabilities.
Anith|||Thanks, Anith, for your reply.
Actually I was trying some data transfer between 2 databases, so the client
option is not feasible for me and length of some column values is more than
a
varchar would permit, so that's not an option either. But knowing it is not
possible is good enough, so I can think of something else.
"Anith Sen" wrote:

> You cannot use concatenation on TEXT datatype. If the length of the value
is
> less than 8000, you can use the CAST function to change it to VARCHAR
> datatype and do the concatenation. Otherwise, you'll have to return them a
s
> distinct values to the client and leverage the client's string concatenati
on
> capabilities.
> --
> Anith
>
>|||You might also want to look at the UPDATETEXT function, but you'll have to
massage each row at a time. The general workaround is to get multiple
VARCHAR columns back like:
SELECT SUBSTRING( textcol, ( 0 * 8000 ) + 1, 8000 ),
SUBSTRING( textcol, ( 1 * 8000 ) + 1, 8000 ),
SUBSTRING( textcol, ( 2 * 8000 ) + 1, 8000 ),
...
Also in SQL 2005 you will have character types with much better
capabilities.
Anith