Conditional Search Criteria

[MMock]

Yes I am back again so soon.

I have a checkbox on my page "show retired only" which if it's checked means I want to filter and only show retired people. The page is already letting you enter criteria like first and last name, etc. So let's say I'm searching on first name Marlene. I would get 170 records. But 20 people named Marlene are retired. So if "show retired only" is checked, I would get 20 records not 170.

I have the argument passed to the stored procedure: @bRetiredOnly bit = 0. My WHERE clause so far (pre-seaching for retired people) looks like this:

	WHERE tbl.FName LIKE '%' + @FirstName + '%' 
	OR tbl.LName LIKE '%' + @LastName + '%' 
	OR tbl.MName LIKE '%' + @MiddleName + '%' 
	OR tbl.Suffix = @Suffix
	OR tbl.Fax LIKE '%' + @Fax + '%'

If I may ask, how do I factor in to look for "tbl.Status = 'R'" if the bit is passed in equal to true?

Thanks.

[MMock]

I guess you can disregard my question. Unless someone dissuades me, I think I want to use dynamic sql, for a number of reasons. One, it will solve this problem. Second, I have search criteria where you can enter a first name or a last name. Or a first and a last name. If you enter first and last, obviously you want to match on both. But I found I am returning matches to just the first name and just the last name. If I enter M Mock, I'm getting all records with first name M even if the last name is Malfoy. So I have to revisit my whole query.

[szlamany]

I would avoid dynamic SQL - it's not your friend...

Why are you against simply making a more "intelligent" WHERE clause.

I do things like this regularly

Where ((@W_MasId Is Null) Or (MA.MasId=@W_Masid)) 
and ((@W_MasName Is Null) Or (MA.MasName=@W_MasName) Or (MA.MasName Like @W_MasName))
and ((@W_MemberSSN Is Null) Or (MA.MemberSSN=@W_MemberSSN))
and (MA.Affil='1') and (isnull(MA.AppLevel,'')='' or @UR_10027 = 1)

Where (PostBatch not in (5,7)) And ((@W_CompId Is Null) Or (CT.CompId=@W_Compid)) 
	and ((@W_CompName Is Null) Or (CO.CompName=@W_CompName) 
	Or (CO.CompName Like @W_CompName))
	and ((IsNull(@DateFor,'') = '') Or (@DateFor = CT.DateFor))
	and ((IsNull(@PostKey,'') = '') Or ((@W_PostDate = CT.PostDate)
	and (@W_PostBatch=CT.PostBatch) and (@W_PostSeq=CT.PostSeq)))

Where ((@W_MasId Is Null) Or (MA.MasId=@W_Masid)) 
and ((@W_MasName Is Null) Or (MA.MasName=@W_MasName) Or (MA.MasName Like @W_MasName))
and ((@W_MemberSSN Is Null) Or (MA.MemberSSN=@W_MemberSSN))
and (@Include=1 or MA.Affil<>'9')

[MMock]

No, you are right. I just haven't coded sql in so long, I've forgotten alternative ways of doing things. That worked fine. But I will have another question pretty soon.