SQLTeam.com | Weblogs | Forums

Needing a dynamic query based on user input


Currently, the relevant query looks like this:
(@fName = '' OR @fName IS NULL OR (DIFFERENCE(first_name, @fName) >= @Diff))
AND (@lName = '' OR @lName IS NULL OR (DIFFERENCE(last_name, @lName) >= @Diff))
AND (@DOB = '' OR @DOB IS NULL OR (dob = @DOB))

So you can see if the parameter is NULL or blank the parameter is basically ignored. If it isn't, it's used with DIFFERENCE to find relevant records.

Now, the customer is requesting that if a wildcard character (*) is present in the parameter, to use a LIKE instead of difference. For the life of me I can't wrap my head around how to structure this query. I understand that at some point, I'll need to replace the asterisks with percent signs, but I have no idea how to make the query act differently based on the presence of one character in a parameter.

Any help is appreciated.


I think this is close to what you're seeking (haven't tested it as you didn't provide table definition, sample data and expected output):

 where ((@fName=''
    or   @fName is null
    or  (@fName like '%[%]%'
   and   first_name like replace(@fName,'*','%')
    or  (@fName not like '%[%]%'
   and   difference(first_name,@fName)>=@Diff
   and ((@lName=''
    or   @lName is null
    or  (@lName like '%[%]%'
   and   last_name like replace(@lName,'*','%')
    or  (@lName not like '%[%]%'
   and   difference(last_name,@lName)>=@Diff
   and (@DOB=''
    or  @DOB is null
    or  dob like replace(@DOB,'*','%')