CREATE PROCEDURE FOR GET-GROUP-DETAILS BY ROLE

CREATE PROCEDURE [dbo].[sp_GroupDetails_byUserID]

      @UserId int

AS

DECLARE @RoleID bigint

BEGIN

      SET NOCOUNT ON;

      BEGIN TRY

            SET @RoleID=(SELECT ROLE_ID

FROM ROLE_MEMBER

WHERE [USER_ID]=@UserId)

            IF @RoleID = 1

                  BEGIN

                        -- select user group1 here!!!

                  END              

            IF @RoleID = 2

                  BEGIN

                         -- select user group2 here!!!

                  END

            IF @RoleID = 3

                  BEGIN

                         -- select user group3 here!!!

                  END              

      END TRY

     

      BEGIN CATCH

            declare @Error varchar(8000)

            set @Error= Convert(varchar,ERROR_NUMBER()) + '*****' + Convert(varchar(4000),ERROR_MESSAGE()) + '*****' + isnull(Convert(varchar,ERROR_PROCEDURE()),'sp_GroupDetails_byUserID') + '*****' + Convert(varchar,ERROR_LINE()) + '*****' + Convert(varchar,ERROR_SEVERITY()) + '*****' + Convert(varchar,ERROR_STATE())

            RAISERROR (@Error, -- Message text.                        

                        16, -- Severity.

                        1 -- State.

                       );

      END CATCH

END