We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ALTER PROCEDURE [dbo].[usp_Permissions_Update_Denied_UserPermissions]
@ColleagueID int,
@PermissionID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

BEGIN TRANSACTION

Declare @RetVal int

    --if the permission exists in the permissions table (means its been granted)
    IF EXISTS(select int_ID from dbo.tbl_Permissions_UserPermissions where int_ColleagueID = @ColleagueID and int_PermissionID = @PermissionID  )
      Declare @oldpermStatus int, @Reason int
      --get the current status of the permission to be denied
      Set @oldpermStatus = (select int_PermissionsStatus from dbo.tbl_Permissions_UserPermissions where int_ColleagueID = @ColleagueID and int_PermissionID = @PermissionID)
        -- if the permstatus is 0 (means requested) then set the deny reason to 1 which is refused by admin
        If (@oldpermStatus = 0) 
          Set @Reason = 1
        else
        -- set the deny reason to 3 which is request removal by authoriser
          Set @Reason = 3
       
        
			-- set the denied permmision to a status of 6 (which is ready to be deleted)
		begin
		   Update dbo.tbl_Permissions_UserPermissions 
		   Set int_PermissionsStatus = 6
		   where int_ColleagueID = @ColleagueID and int_PermissionID = @PermissionID	
			IF @@ERROR <> 0 GOTO ErrBlock
		end
	  -- find the ID of the permission and check it has a status of 6.  if it does then move it to the denied table
	 IF exists(select int_ID from dbo.tbl_Permissions_UserPermissions where int_ColleagueID = @ColleagueID and int_PermissionsStatus = 6)

		Exec @RetVal = dbo.usp_Permissions_Admin_Move_Denied_UserPermissions @ColleagueID, @Reason
		 IF @RetVal <> 0 GOTO ErrBlock

  
COMMIT TRANSACTION
Return

ErrBlock:
ROLLBACK TRANSACTION
Return

End

ALTER PROCEDURE [dbo].[usp_Permissions_Admin_Move_Denied_UserPermissions]
	-- Add the parameters for the stored procedure here
@ColleagueID int,
@Reason int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

Declare @RetVal int, @interror int
Set @interror = 0

       Insert into dbo.tbl_Permissions_UserPermissionsDenied (int_ColleagueID,int_PermissionID,int_DenyStatus,dte_DeniedOn)
	   values (@ColleagueID,(Select int_PermissionID from dbo.tbl_Permissions_UserPermissions where int_ColleagueID = @ColleagueID and int_PermissionsStatus in (5)),@reason,getdate())
		IF (@@ERROR <> 0) BEGIN
			Set @interror = 1
		END
		
		If(@interror = 0) BEGIN
			Delete from dbo.tbl_Permissions_UserPermissions where int_ColleagueID = @ColleagueID and int_PermissionsStatus in (6)
		END

If (@interror = 0)		
RETURN 0		
else
RETURN 1
End

We walk in the dark places no others will enter
We stand on the bridge and no one may pass