Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Transactions Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 3:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 42,484, Visits: 35,553
Dave Hall (1/25/2013)
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)?


Sure it will, but you have nothing that checks to see if the Insert throws an error or not. There's no checks for @@Error after the insert, so no one notices that it threw an error. Hence it looks, to your code, that the inner proc ran fine and hence gets committed.

@@Error - the error code of the previous statement that ran.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

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

Post #1411579
Posted Friday, January 25, 2013 3:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 5:04 AM
Points: 5, Visits: 14
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!!
Post #1411584
Posted Friday, January 25, 2013 5:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 42,484, Visits: 35,553
Yup, that's about it for SQL 2000's error handling. Fun, isn't it?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

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

Post #1411635
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse