Using a function in a query that returns a string or null
I want to join 2 tables 'addresses'
and 'user_info'
on user_id
and app_id
(is it a number or it is zero) like these two 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
That app_id must be tricky and I wrote a function to return it. It returns a string that will be, for example, "equal to zero" or "= 1234". I'm trying to call this with the 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 am getting this error:
Msg 4145, Level 15, State 1, Line 3 An expression of a non-boolean type specified in a context where a condition close to 'dbo' is expected.
I want the query to be very simple, since it shouldn't determine if the function returns null or not.
Can you suggest a better way to keep a very simple call request?
(I'm in sql server 2005.)
So, if fnGetAppId is null, this request looks like this:
select * from user_info left outer join addresses on addresses.user_id = user_info.user_id and null
I doubt this is what you want. :)
You can do a simple check in your logic before invoking the request to properly handle null for fnGetAppId, and as Clyde said, you also need the = sign for non-empty
...
source to share
As James Black pointed out, you have an AND where you probably want a WHERE. Apart from that, I suggest you make the function boolean (by passing it the .app_id address as another argument), so it can do IS NULL or = 1234 if needed (Bill COALESCE's solution is really smart, but the mapping inside the function is easier. IMO).
source to share