Create 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

    --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)
		   Update dbo.tbl_Permissions_UserPermissions 
		   Set int_PermissionsStatus = 6
		   where int_ColleagueID = @ColleagueID and int_PermissionID = @PermissionID
			IF @@ERROR <> 0 GOTO ErrBlock
			
	  -- 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 dbo.usp_Permissions_Admin_Move_Denied_UserPermissions @ColleagueID, @Reason
		 IF @@ERROR <> 0 GOTO ErrBlock
   
COMMIT TRANSACTION
Return

ErrBlock:
ROLLBACK TRANSACTION
Return

End

Create 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

    BEGIN TRANSACTION 
    
      
       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 GOTO ErrBlock

			Delete from dbo.tbl_Permissions_UserPermissions where int_ColleagueID = @ColleagueID and int_PermissionsStatus in (6)
			IF @@ERROR <> 0 GOTO ErrBlock		

	COMMIT TRANSACTION 
	Return
	
ErrBlock:
ROLLBACK
RETURN  

End

Msg 515, Level 16, State 2, Procedure usp_Permissions_Admin_Move_Denied_UserPermissions, Line 21
Cannot insert the value NULL into column 'int_PermissionID', table 'dbo.tbl_Permissions_UserPermissionsDenied'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 266, Level 16, State 2, Procedure usp_Permissions_Admin_Move_Denied_UserPermissions, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 2, current count = 0.
Msg 3902, Level 16, State 1, Procedure usp_Permissions_Update_Denied_UserPermissions, Line 46
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Msg 3903, Level 16, State 1, Procedure usp_Permissions_Update_Denied_UserPermissions, Line 53
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

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

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

Create 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

    --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)
		   Update dbo.tbl_Permissions_UserPermissions 
		   Set int_PermissionsStatus = 6
		   where int_ColleagueID = @ColleagueID and int_PermissionID = @PermissionID
			IF @@ERROR <> 0 GOTO ErrBlock
			
	  -- 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 dbo.usp_Permissions_Admin_Move_Denied_UserPermissions @ColleagueID, @Reason
		 IF @@ERROR <> 0 GOTO ErrBlock
   
COMMIT TRANSACTION
Return

ErrBlock:
ROLLBACK TRANSACTION
Return

End

Create 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
     
       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())

			Delete from dbo.tbl_Permissions_UserPermissions where int_ColleagueID = @ColleagueID and int_PermissionsStatus in (6)

End