mjhjmtu

I'm trying to make a stored procedure who will return me the RowID with most matching parameters. Let's say we have a table:

+-------+------------+------------+-----------+
| RowID | DocumentID | EmployeeID | CompanyID |
+-------+------------+------------+-----------+
| 1 | 36 | 1 | 4 |
| 2 | 36 | null | 2 |
| 3 | 36 | 4 | null |
+-------+------------+------------+-----------+

And I send let's say values DocumentID = 36 ,EmployeeID = 5, ComanyID = 2,
it should return me second row since DocumentID and CompanyID exist.

Some other situation would be, if I send DocumentID = 36, EmployeeID = 5 and CompanyID=10, it should return me first in the table.

If there is too little information about this problem, or it's not clear, feel free to ask for more detail.

;WITH c AS
(
 SELECT RowID, 
 c = CASE WHEN DocumentID = @DocumentID THEN 1 ELSE 0 END
 + CASE WHEN EmployeeID = @EmployeeID THEN 1 ELSE 0 END
 + CASE WHEN CompanyID = @CompanyID THEN 1 ELSE 0 END
 FROM dbo.TableName
)
SELECT TOP (1) RowID FROM c ORDER BY c DESC;

Try something like this - but see if you can understand the concept.

SELECT TOP (1) RowID
FROM
(
SELECT RowID
FROM YourTable
WHERE DocumentID = @DocumentID
UNION ALL
SELECT RowID
FROM YourTable
WHERE EmployeeID = @EmployeeID
UNION ALL
SELECT RowID
FROM YourTable
WHERE CompanyID = @CompanyID
) r
GROUP BY RowID
ORDER BY COUNT(*) DESC, RowID ASC;