Return a value if no rows match

Question

The [authorityID] (TinyInt) column will never be null.

What I want is to return a 15 if there are no rows. With the query below I get nothing if there are no rows:

select top 1 isnull([authorityID],15) 
from [docAuthority] with (nolock) 
where [grpID] = 0 and [sID] = 42

score 3 · Accepted Answer · answered Jul 28 '14 at 17:18

SELECT  authorityId = isnull(( SELECT   [authorityID]
                               FROM     [docAuthority] WITH ( NOLOCK )
                               WHERE    [grpID] = 0
                                        AND [sID] = 42
                             ), 15)

score 1 · Answer 2 · answered Jul 28 '14 at 16:54

1

As you noted, if the query returns no rows, there's nothing to apply the isnull on. One dirty trick is to use union all and (ab)use the top construct:

SELECT TOP 1 authorityID
FROM   (SELECT authorityID
        FROM   [docAuthority] WITH (nolock) 
        WHERE  [grpID] = 0 AND [sID] = 42
        UNION ALL
        SELECT 15) t

answered Jul 28 '14 at 16:54

Mureinik

297,002
52
306
350

Not as slick as I would hope for but I don't think there is anything better. Let me give it a couple hours for a trick before I accept this answer. P.S. Since I am reading into .NET I will do it there if there is not a more direct way to do in TSQL. – paparazzo Jul 28 '14 at 17:02

Return a value if no rows match

2 Answers2

Linked