SQL Server: How to call a UDF, if available?

Question

Most systems will have a user-defined function (UDF) available. Some will not. i want to use the UDF if it's there:

SELECT 
    Users.*,
    dbo.UserGroupMembershipNames(Users.UserID) AS MemberOfGroupNames
FROM Users

Otherwise fallback to the acceptable alternative

SELECT
   Users.*,
   (SELECT TOP 1 thing FROM Something 
    WHERE Something.ID = Users.UserID) AS MemberGroupNames
FROM Users

How do?

---

My first attempt, using the obvious solution, of course failed:

SELECT 
    Users.*,
    CASE
    WHEN (OBJECT_ID('dbo.UserGroupMembershipNames') IS NOT NULL) THEN
           dbo.UserGroupMembershipNames(Users.UserID)
    ELSE (SELECT TOP 1 thing FROM Something 
          WHERE Something.ID = Users.UserID)
    END AS MemberOfGroupNames
FROM Users

for reasons beyond me

Answer 1

This is because the engine is attempting to bind to the UDF, either during the parsing or preparation of the execution plan.

You will have to split it into two statements with a conditional (obvisously this will not work in a view or inline table-valued function, unfortunately):

IF (OBJECT_ID('dbo.UserGroupMembershipNames') IS NOT NULL)
SELECT  
    Users.*, 
    dbo.UserGroupMembershipNames(Users.UserID) 
    AS MemberOfGroupNames 
FROM Users 
ELSE
SELECT  
    Users.*, 
    (SELECT TOP 1 thing FROM Something  
          WHERE Something.ID = Users.UserID) 
    AS MemberOfGroupNames 
FROM Users