Stored proc to return data based on nullable columns by a priority

Question

I have a table called ClientUrls that has the following structure:

+------------+----------------+----------+
| ColumnName |    DataType    | Nullable |
+------------+----------------+----------+
| ClientId   | INT            | No       |
| CountryId  | INT            | Yes      |
| RegionId   | INT            | Yes      |
| LanguageId | INT            | Yes      |
| URL        | NVARCHAR(2048) | NO       |
+------------+----------------+----------+

I have a stored procedure up_GetClientUrls that takes the following parameters:

@ClientId INT
@CountryId INT
@RegionId INT
@LanguageId INT

Information about the proc

1. All of the parameters are required by the proc and none of them will be NULL
2. The aim of the proc is to return a single matching row in the table based on a pre-defined priority. The priority being ClientId>Country>Region>Language
3. Three of the colums in the ClientUrls table are nullable. If one column contains a NULL, it refers to "All". e.g. if LanguageId IS NULL, then it refers to "AllLanguages". So if we send a LanguageId of 5 to the proc, we look for that first, otherwise we try and find the one that is NULL.

Matrix of priority (1 being first)

+---------+----------+-----------+----------+------------+
| Ranking | ClientId | CountryId | RegionId | LanguageId |
+---------+----------+-----------+----------+------------+
|       1 | NOT NULL | NOT NULL  | NOT NULL | NOT NULL   |
|       2 | NOT NULL | NULL      | NOT NULL | NOT NULL   |
|       3 | NOT NULL | NOT NULL  | NULL     | NOT NULL   |
|       4 | NOT NULL | NULL      | NULL     | NOT NULL   |
|       5 | NOT NULL | NOT NULL  | NOT NULL | NULL       |
|       6 | NOT NULL | NULL      | NOT NULL | NULL       |
|       7 | NOT NULL | NULL      | NULL     | NULL       |
+---------+----------+-----------+----------+------------+

Here is some example data:

+----------+-----------+----------+------------+-------------------------------+
| ClientId | CountryId | RegionId | LanguageId |             URL               |
+----------+-----------+----------+------------+-------------------------------+
|        1 |         1 | 1        | 1          | http://www.Website.com        |
|        1 |         1 | 1        | NULL       | http://www.Otherwebsite.com   |
|        1 |         1 | NULL     | 2          | http://www.Anotherwebsite.com |
+----------+-----------+----------+------------+-------------------------------+

Example stored proc call

EXEC up_GetClientUrls   @ClientId = 1
                        ,@CountryId = 1
                        ,@RegionId = 1
                        ,@LanguageId = 2

Expected response (based on example data)

+----------+-----------+----------+------------+-------------------------------+
| ClientId | CountryId | RegionId | LanguageId |             URL               |
+----------+-----------+----------+------------+-------------------------------+
|        1 |         1 |     NULL | 2          | http://www.Anotherwebsite.com |
+----------+-----------+----------+------------+-------------------------------+

This row is returned because matching on a NULL RegionId with the correct LanguageId is a higher priority than matching on a NULL LanguageId with the correct RegionId.

Here is the code for the proc (which works). To actually get to my question, is there a better way to write this? If i extend this table in future, I'm going to just keep multiplying the number of UNION statements and therefore it isn't really scalable.

Actual stored procedure

CREATE PROC up_GetClientUrls
    (
        @ClientId       INT
        ,@CountryId     INT
        ,@RegionId      INT
        ,@LanguageId    INT
    )
AS
    BEGIN

        SELECT TOP 1
            prioritised.ClientId
            ,prioritised.CountryId
            ,prioritised.RegionId
            ,prioritised.LanguageId
            ,prioritised.URL
        FROM
        (
            SELECT
                c.ClientId
                ,c.CountryId
                ,c.RegionId
                ,c.LanguageId
                ,c.URL
                ,1  [priority]
            FROM ClientUrls c
            WHERE c.ClientId = @ClientId
            AND c.CountryId = @CountryId
            AND c.RegionId = @RegionId
            AND c.LanguageId = @LanguageId
            UNION
                SELECT
                    c.ClientId
                    ,c.CountryId
                    ,c.RegionId
                    ,c.LanguageId
                    ,c.URL
                    ,2  [priority]
                FROM ClientUrls c
                WHERE c.ClientId = @ClientId
                AND c.CountryId IS NULL
                AND c.RegionId = @RegionId
                AND c.LanguageId = @LanguageId
            UNION
                SELECT
                    c.ClientId
                    ,c.CountryId
                    ,c.RegionId
                    ,c.LanguageId
                    ,c.URL
                    ,3  [priority]
                FROM ClientUrls c
                WHERE c.ClientId = @ClientId
                AND c.CountryId = @CountryId
                AND c.RegionId IS NULL
                AND c.LanguageId = @LanguageId
            UNION
                SELECT
                    c.ClientId
                    ,c.CountryId
                    ,c.RegionId
                    ,c.LanguageId
                    ,c.URL
                    ,4  [priority]
                FROM ClientUrls c
                WHERE c.ClientId = @ClientId
                AND c.CountryId IS NULL
                AND c.RegionId IS NULL
                AND c.LanguageId = @LanguageId
            UNION
                SELECT
                    c.ClientId
                    ,c.CountryId
                    ,c.RegionId
                    ,c.LanguageId
                    ,c.URL
                    ,5  [priority]
                FROM ClientUrls c
                WHERE c.ClientId = @ClientId
                AND c.CountryId = @CountryId
                AND c.RegionId = @RegionId
                AND c.LanguageId IS NULL
            UNION
                SELECT
                    c.ClientId
                    ,c.CountryId
                    ,c.RegionId
                    ,c.LanguageId
                    ,c.URL
                    ,6  [priority]
                FROM ClientUrls c
                WHERE c.ClientId = @ClientId
                AND c.CountryId IS NULL
                AND c.RegionId = @RegionId
                AND c.LanguageId IS NULL
            UNION
                SELECT
                    c.ClientId
                    ,c.CountryId
                    ,c.RegionId
                    ,c.LanguageId
                    ,c.URL
                    ,7  [priority]
                FROM ClientUrls c
                WHERE c.ClientId = @ClientId
                AND c.CountryId IS NULL
                AND c.RegionId IS NULL
                AND c.LanguageId IS NULL
        ) prioritised
        ORDER BY prioritised.[Priority]

    END

Answer 1

This is easy (if i understand you correctly). You can do it with very little code. Plus it would be easy to extend if needed.

Here is a working example

--Make a table
CREATE TABLE #ClientUrls (ClientId INT NOT NULL,CountryId INT NULL,RegionId INT NULL,LanguageId INT NULL,URL NVARCHAR(2048) NOT NULL)

--Put some data into it
INSERT INTO #ClientUrls (ClientId, CountryId, RegionId, LanguageId, URL)
VALUES
(1,1,1,1,'http://www.Website.com'),
(1,1,1,NULL,'http://www.Otherwebsite.com'),
(1,1,NULL,2,'http://www.Anotherwebsite.com')

--This would all be in your proc
----------------------------------------------
DECLARE @ClientId       INT = 1
DECLARE @CountryId      INT = 1
DECLARE @RegionId       INT = 1
DECLARE @LanguageId     INT = 2

--This is the interesting bit
----------------------------------------------
SELECT TOP 1 C.*

FROM    #ClientUrls AS C
ORDER BY 
    --Order the ones with the best hit count near the top
   IIF(ISNULL(C.ClientId,  @ClientId)   = @ClientId  ,1,0) +            
   IIF(ISNULL(C.CountryId, @CountryId)  = @CountryId ,2,0) +
   IIF(ISNULL(C.RegionId,  @RegionId)   = @RegionId  ,4,0) +
   IIF(ISNULL(C.LanguageId,@LanguageId) = @LanguageId,8,0) DESC,

    --Order the ones with the least nulls of each hit count near the top
   IIF(C.ClientId   IS NULL,0,1) +                                              
   IIF(C.CountryId  IS NULL,0,2) +
   IIF(C.RegionId   IS NULL,0,4) +
   IIF(C.LanguageId IS NULL,0,8) DESC

DROP TABLE #ClientUrls

Thats it. In older versions of SQL you cant use IIF, but you can replace that with a case statement if needed.

It works like this.

Each matching item is given a value (a bit like in a binary number) Then based on each matching item we use the value or 0 if its not a match by adding up the total we will always pick the best combination of matches.

value          1           2           4         8            Total value 
+---------+----------+-----------+----------+------------+
| Ranking | ClientId | CountryId | RegionId | LanguageId |
+---------+----------+-----------+----------+------------+
|       1 | NOT NULL | NOT NULL  | NOT NULL | NOT NULL   |       15
|       2 | NOT NULL | NULL      | NOT NULL | NOT NULL   |       13
|       3 | NOT NULL | NOT NULL  | NULL     | NOT NULL   |       11  
|       4 | NOT NULL | NULL      | NULL     | NOT NULL   |       9
|       5 | NOT NULL | NOT NULL  | NOT NULL | NULL       |       7
|       6 | NOT NULL | NULL      | NOT NULL | NULL       |       5
|       7 | NOT NULL | NULL      | NULL     | NULL       |       1
+---------+----------+-----------+----------+------------+

I just updated this to make sure that you get the Non null version over a null option.

If you edit the results to return more then the top 1 you can see the items in the correct order. Ie if you change language from 2 to 1 you will get the 1,1,1,1 line Over the 1,1,1,Null option

Answer 2

Not tested but you could do something like this:

SELECT TOP 1 c.ClientId,
        c.CountryId,
        c.RegionId,
        c.LanguageId,
        c.URL
FROM ClientUrls c
ORDER BY CASE 
            WHEN c.ClientId = @ClientId
                THEN 1000
            ELSE 0
            END + 
        CASE 
            WHEN c.CountryId = @CountryId
                THEN 200
            WHEN c.CountryId IS NULL
                THEN 100
            ELSE 0
            END + 
        CASE 
            WHEN c.RegionId = @RegionId
                THEN 20
            WHEN c.CountryId IS NULL
                THEN 10
            ELSE 0
            END +  
        CASE 
            WHEN c.LanguageId = @LanguageId
                THEN 2
            WHEN c.CountryId IS NULL
                THEN 1
            ELSE 0
            END DESC

By giving a value to each match and selecting the highest value you could reduce the code needed. But you will increase the number of case statements needed, instead of the amount of unions.

This could also be a function instead of a stored procedure. So it could be used more easier in other query's