How to check if a field in subquery in SQL?

How to check if a field in subquery in SQL?

I have to do a lot of queries with this kind of logic:
Check if a table contains a record for a patient
If it does return then 'Yes'
Else return 'No'
Now, I want to create a procedure that will do this, so I tried to create
a function that will do the above, but ended up in dynamic queries which
is not possible in functions.
Is it possible to achieve this? How can I go about this?
PS: Maybe something like:
select
(IF EXISTS(SELECT * FROM Dtl_Patient WHERE Pk = 3990 select 'Yes' else
select 'No')) as output from dtl_AllPatient;
Thanks
Thanks