Check User in Role?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: John Rugo (jrugo_at_patmedia.net)
Date: 08/26/04 

Date: Thu, 26 Aug 2004 14:41:49 -0400

Hi All,
I have the following requirement:
I need to know if the current user is within a particular Role.
Example:

Create Procedure dbo.usp_Test_User
AS

SELECT (CASE WHEN
                            USER_NAME In Good_Role Then '1'
                 Else '0'
                END AS [InRole]

GO

Any help would be great!
Thanks,
John.