Does there exist a user with this role? I'm trying to write a query which will return a boolean value if there exists any user which has this specific role.

For example is there any user hwo's role is 'role1'

This is how far i got 

SELECT CASE WHEN EXISTS 
(SELECT --'Here i don't know how to select all user's which have a specific role'--    )
GO


And if it's possible to check user's from only one database .
It's old code, but it checks out:

SELECT 
    user_name(member_principal_id) as memberName
FROM
    sys.database_role_members
WHERE 
    user_name(role_principal_id) = 'db_owner'


So, in full:

    DECLARE @roleName varchar(20) = 'MF_Pilots'
    DECLARE @user varchar (20) = 'Rey'
SELECT CASE WHEN EXISTS
    (SELECT 
    user_name(member_principal_id) as memberName
FROM
    sys.database_role_members
WHERE 
    user_name(role_principal_id) = @roleName
AND 
    user_name(member_principal_id) = @user)
    THEN 1 
    ELSE 0
    END


That'll do what you want. Stick it in a function and profit.

Taken from https://stackoverflow.com/questions/3361778/get-list-of-all-database-users-with-specified-role