Conditional row selection

Question

I am trying to select the most applicable (most generic) row from a table

CREATE TABLE #T(Id int, Bid int, Did int,Pid int, Vid int, SomeData varchar(10))

INSERT INTO #T
VALUES(1,1,1,1,1,'Data1'),
      (2,1,1,NULL,5,'Data2'),
      (3,1,1,NULL,6,'Data3'),
      (4,1,1,8,NULL,'Data4'),
      (5,1,NULL,NULL,NULL,'Data5')

SELECT *
FROM #T

When I used the parameter values as below, the row I am expecting is with Id 1, As it has the all the values passed from the parameters.

DECLARE @Bid INT=1,
        @Did INT=1,
        @Pid INT=1,
        @Vid INT=1

When I use below values the row I am expecting is with Id 5. Because there is no row matching Pid 1 and Vid Null. So the most generic row for these parameters are the one with Id =5.

DECLARE @Bid INT=1,@Did INT =1,@Pid INT =1,@Vid INT =NULL

OR

DECLARE @Bid INT=1, @Did INT=1,@Pid INT=1,@Vid INT=6

Column value NULL means it can be applicable to any parameter value.

e.g If all has value null mean it is kind of master row. But if master "SomeData" needs to be different, lets say for Vid 1, then we create row will all values null but the Vid with Value 1.

Priority of the column are from Left to Right.

Also if the values are

DECLARE @Bid INT=1,@Did INT =1,@Pid INT =10,@Vid INT =1

The expected row is the one with id 5.

I have tried below query but it returns multiple rows. Is there anyway to achieve something like this

SELECT *
FROM #T
WHERE ((Bid = @Bid) OR Bid IS NULL) 
    AND ((Did = @Did) OR Did IS NULL)   
    AND ((Pid = @Pid) OR Pid IS NULL)   
    AND ((Vid = @Vid) OR Vid IS NULL)   

Answer 1

If you don't need to worry about duplicates, i.e. more than one row having the same number of matches, then you can try the following query:

SELECT TOP 1 t.Id
FROM
(
    SELECT Id,
           CASE WHEN Bid = @Bid OR (Bid IS NULL AND @Bid IS NOT NULL)
                THEN 1 ELSE 0 END AS BidMatch
           CASE WHEN Did = @Did OR (Did IS NULL AND @Did IS NOT NULL)
                THEN 1 ELSE 0 END AS DidMatch
           CASE WHEN Pid = @Pid OR (Pid IS NULL AND @Pid IS NOT NULL)
                THEN 1 ELSE 0 END AS PidMatch
           CASE WHEN Vid = @Vid OR (Vid IS NULL AND @Vid IS NOT NULL)
                THEN 1 ELSE 0 END AS VidMatch
    FROM #T
) t
ORDER BY t.BidMatch DESC,
         t.DidMatch DESC,
         t.PidMatch DESC,
         t.VidMatch DESC

You can just remove the TOP 1 clause at the beginning of the query to see all IDs sorted descending by how close they match the input variables.