Thanks, Gail. In a way it's a comfort to know its not just me being a pleb with this. It sounded so simple at the start!!!

I have just tested your suggestion with regards to have the transaction in 1 and not the other but hit a problem....

SP with transaction:

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

and the other:

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

I'm calling the 1st Proc like before and the transaction errors are gone (yay) BUT the Delete statement still executes and is not rolled back (boo).

I thought that if the call to the Proc was within a transaction then it would Rollback everything within (including actions carried out by a called SP)?

I really appreciate your insight and patience with this!

@Bhuvnes - I could do that, so I've read, but am trying to learn a little bit more..... perhaps running before I can walk here!