I have 3 parameter fields, last-name, middle-name, first-name
and the view/table of database has just one string combined of all
three(and it is NOT possible to split).

I need to provide search facility with any combination of these three
fields.

I am very new to this environs and would like to know how I can
achieve this.

Do I have to create an SP which checks if each of the fields is NULL
and do accordingly ?

any help will be appreciated

Thanks

If I understand what you want correctly you could do this:

select * from yourtable where name like '%' + @FirstName + '%' + @MiddleName
+ '%' + @LastName + '%'

The above query doesn't care if a parameter is null, or has a space or a
partial first name, partial lastname etc (I don't know if they are putting
in the names freeform or picking from a listbox). Anyway, that should at
least give you an idea.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services


[quoted text, click to view]

Hello Bruce,

Thanks for the advice. I have been trying queries in those lines but I
dont get the results.
I get the result only in the case where the Lastname, Middlename and
Firstname match.

for example
I have tried the following:

Name LIKE '%' + @last + '%' + @middle + '%' + @first + '%' ----> only
matches if all strings are provided.

Name LIKE '%' + @last + '%' + @first + '%' ----> matches all with the
last and first

etc..

I can use an OR to consider all possibilities but when i have to
consider the cases when the user gives a single param i will always get
a bunch of results even when the user gives the fullname

For this reason i would like to know if I can put some PL/SQL logic for
diff cases but seems like that is not the way to go as my query is not
being accepted.
Is there any other way I can do this ? a Stored P ? any ideas how to
do it ?

Thanks very much
bofo

I go it working. Using a stored procedure.

thanks