Trying to find a default row

[tshad]

I have a table where I would like to get only one row from a table where multiple parameters are passed and it is possible to get multiple rows. I would like to get the row with the greatest amount of specificity. I would set up the order of the results in the order I would like the tests done. Once a test passes, there are no other tests done.

Here is an example query:

CREATE TABLE MessageTest
( ID int IDENTITY(1,1),
Name varchar(50),
Number int,
Class varchar(50)
)
INSERT MessageTest (Name,Number,Class) VALUES ('Main',NULL,NULL)
INSERT MessageTest (Name,Number,Class) VALUES ('Main',NULL,'MainClass')
INSERT MessageTest (Name,Number,Class) VALUES ('Main',2,'MainClass')
INSERT MessageTest (Name,Number,Class) VALUES ('Main',1,NULL)

DECLARE @Name varchar(100),
@Number int,
@Class varchar(100)

SELECT @Name = 'Main',
@Number = 4,
@Class = 'MainClass'

SELECT ID,
Name,
Number,
Class
FROM MessageTest
WHERE ((@Name IS NOT NULL AND @Number IS NOT NULL AND @Class IS NOT NULL)
AND
(
(@Name LIKE Name + '%' AND @Number= Number AND @Class LIKE Class + '%')
OR
(@Name LIKE Name + '%' AND Number IS NULL AND @Class LIKE Class + '%')
OR
(@Name LIKE Name + '%' AND @Number = Number AND Class IS NULL)
OR
(@Name LIKE Name + '%' AND Number IS NULL AND Class IS NULL)
)
)


This query would pass back 2 rows:

1 Main NULL NULL
2 Main NULL MainClass

I would like to only get the row with ID = 2. I only want the row with ID = 1 if the other 3 tests don't find a record. If the second query finds something, the other 2 test shouldn't return anything. Always giving me only one message.

There will actually be multiple sets based on how many parameters are passed.

But in this case I want to pass back a record if all 3 parameters match.
If not then, if the 1st and 3rd parameter pass and there is a null value in the 2nd column.
If not then, if the 1st and 2nd parameter pass and there is a null in the 3rd column.
If none of the above pass then if the 1st one passes and the 2nd and 3rd parameters are both null.

Is there a way to structure the where clause to do this?

Thanks,

Tom

 

[tshad]

A dynamic query wouldn't help. The issue isn't the number of parameters I send. It is the result that I get back. I have no way of knowing how many records I would get back (could be 0 - 4 records).

If I change the @Number = 2 instead of 4 and run the query I would get:

1 Main NULL NULL
2 Main NULL MainClass
3 Main 2 MainClass

In this case, I would want the row where ID = 3, as that matches all the parameters.

Thanks,

Tom