tags:

views:

40

answers:

2

I have SP that selects data from a table with two columns like this

@param1 text,
@param2 text
Select * from tablle1 where field1 like @param1 and field2 like @param2

However , because sometimes Filed2 is null i had to do this , otherwise the query returned nothing

@param1 text,
@param2 text
Select * from tablle1 where field1 like @param1 and (field2 like @param2 OR field2 IS NULL)

My problem is that if I now try to run the SP with a value for @param2 and '%' for @param1 i get all the rows from the table

How can i make this in every situation ? I intend to use this SP in a search form on a asp.net site, and give the users the option to use '%' in searches.(eventually there would me more fields to search for and each one will have a textbox on the page that defaults to '%')

+2  A: 

try this

@param1 text,
@param2 text
Select * from tablle1 where field1 like @param1 and isnull(field2,@param2) like @param2 
or
Select * from tablle1 where field1 like @param1 and isnull(field2,'') like @param2 
Pranay Rana
the problem i have with this method is that filed2 is numeric while the @param2 is text (for allowing to pass '%'). I get a text is incompatible with numeric error.
Iulian
@Iulian- than first cast your field to varchar by using Cast or Convert function
Pranay Rana
i've change it to `Select * from tablle1 where field1 like @param1 and isnull(field2,0) like @param2` and it's working, from my understanding this will make the second condition like `0 = @param2` if the filed2 is empty ?
Iulian
@Iulian -try it out may this will work because there is no syntax error
Pranay Rana
Yeah it is indeed working , I'm not sure i really understand why :) Is it because the `0 = @param2` will not have any effect on the select ?
Iulian
A: 

you should take a look at Sommarskogs homepage

It is the best reference on the subject, and it will also solve some of the other problems you most probably are having with this code.

Henrik Staun Poulsen