Looking for a more concise query

[dand11]

I have a form which is used for searching for records in the database. There is no gaurantee which fields will be filled in on the form when the search commences. Right now I have the query set up to join tables and return all rows to a temp table then I have a conditional for each field to test wether it is null. If it isn't I delete the records that don't have that value for that specific field. Is there a more concise way to do this? Here's a sample of what I'm doing. It's in ANSI SQL but it should be difficult to see what I'm doing:

Insert into #tmpNickNameOnly(blnDeleted,LINK)
			Select 0, m.LINK from MNAMES m
			Left Join Aliases a on a.LINK = m.LINK and a.Alias = @Nickname 
			where @FName is null OR SUBSTRING(m.NAME,POSITION(',' IN m.NAME)+2,30) LIKE @FName + '%'
			AND m.LINK <> 0; 
			
						
			If @LName is not null then
			   Delete #tmpNickNameOnly set blnDeleted = 1 where LINK NOT IN (Select LINK from mnames where SUBSTRING(NAME,1,POSITION(',' IN NAME)-1) = @LName);
			End If;
			
			If @DOBBegin is not null then
			   Delete #tmpNickNameOnly set blnDeleted = 1 where LINK NOT IN (Select LINK from mnames where DOB Between @DOBBegin and @DOBEnd);
			End If;
			
			If @Sex is not null then
			   Delete #tmpNickNameOnly set blnDeleted = 1 where LINK NOT IN (Select LINK from mnames where sex = @Sex);
			End If;
			
			If @Race is not null then
			   Delete #tmpNickNameOnly set blnDeleted = 1 where LINK NOT IN (Select LINK from mnames where Race = @Race);
			End If;

 

[dand11]

The delete statements were actually updates, I changed it when posting to try to make it easier to understand and didn't see the rest of the query. I'm using Advantage for this and it sets the records as deleted instead of removing them from the temp tables so I'm having to update them with a bool value to return the ones not set to 1.

 

[RiverGuy]

Yes, here is an example:

SELECT * FROM YourTable
WHERE
(@Parameter1 IS NULL OR Column1 = @Parameter1)
AND
(@Parameter2 IS NULL OR Column2 = @Parameter2)
AND
(@Parameter3 IS NULL OR Column3 = @Parameter3)

So when this query runs, if one of your parameters is NULL, then the result set will not be filtered on that column. If it is not null, then it will filter on that column and parameter.

 

[dand11]

Thx