Using a function in a query that returns a string or a null

I want to join 2 tables 'addresses' and 'user_info' on user_id and app_id (which is a number, or it is null), like these 2 examples:

select * from user_info 
left outer join addresses on addresses.user_id = user_info.user_id 
and addresses.app_id is null

select * from user_info 
left outer join addresses on addresses.user_id = user_info.user_id 
and addresses.app_id = 1234

What the app_id should be is complicated and I have written a function to return it. It returns a string, which would be for example "is null" or "= 1234". I'm trying to call it with this syntax:

select * from user_info 
left outer join addresses on addresses.user_id = user_info.user_id 
and addresses.app_id dbo.fnGetAppId(addresses.user_id)

I get this error:

Msg 4145, Level 15, State 1, Line 3 An expression of non-boolean type specified in a context where a condition is expected, near 'dbo'.

I'd like to keep the query very simple as it is without having to determine if the function is returning a null or not.

Can you suggest the best way to keep the calling query very simple?

(I'm in sql server 2005.)

From stackoverflow

• It looks like you're just missing an = sign

  addresses.app_id dbo.fnGetAppId(addresses.user_id)

  rather than

  addresses.app_id = dbo.fnGetAppId(addresses.user_id)

• So if fnGetAppId is null then this query looks like the following?

  select * from user_info left outer join addresses on addresses.user_id = user_info.user_id and null
  

  I doubt that is what you want. :)

  You may want to do a simple check in your logic, before calling the query, to properly handle a null for fnGetAppId and as Clyde mentioned you also need an = sign for a non-null

  .

• NULL != NULL. If either address.app_id = NULL or fnGetAppID = NULL, the comparison will fail. I would write the comparison as:

  coalesce(address.app_id, 'NULLSTRING') = coalesce(dbo.fnGetAppID(addresses.user_id), 'NULLSTRING')

• As James Black pointed out, you have AND where you probably want WHERE. Beyond this, I suggest you make the function a boolean one (passing address.app_id to it as one more argument), so it can perform an IS NULL or = 1234 as appropriate (Bill's COALESCE solution is clever indeed, but putting the appropriate comparison inside the function is more straightforward, IMO).