Wednesday, September 5, 2012

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

No comments: