Thank you, both for your help. I think perhaps I was trying to do too much for my first attempt at this!

Here is how I've changed the code and it now works:

SP1

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

SP2

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

I'm sure there is a much nicer way and a better way of doing it but I don't know it!! 😛