Stored Procedure/ Transaction problem

[oddball]

Have written a stored procedure that submits Company info into a Company Table, User info into a User Table, and two inserts into a UserRoles table. What i want to achieve is if either the User or Company INSERTS fail becuase of a duplicate field in Email or CompanyName, then all four inserts will be not occur/ rolled back.

What i have written doesn't work because even if one fails it still INSERTS the rest of the info.

Heres my code, what am i doing wrong?


===========================================================


ALTER Procedure AddMember

(
@Name nvarchar(50),
@Email nvarchar(100),
@Password nvarchar(20),
@CompanyName nvarchar(50),
@Address1 nvarchar(100),
@Address2 nvarchar(100),
@Address3 nvarchar(100),
@Town_City nvarchar(50),
@Postcode nvarchar(10),
@Country nvarchar(50),
@TelNum nvarchar(15),
@FaxNum nvarchar(15),
@CompanyEmail nvarchar(100),
@WebSite nvarchar(100),
@Contact1 nvarchar(50),
@Position1 nvarchar(50),
@Email1 nvarchar(100),
@Tel1 nvarchar(15),
@Contact2 nvarchar(50),
@Position2 nvarchar(50),
@Email2 nvarchar(100),
@Tel2 nvarchar(15),
@Contact3 nvarchar(50),
@Position3 nvarchar(50),
@Email3 nvarchar(100),
@Tel3 nvarchar(15),
@MemberRates int,
@Payment int,
@ShowToAll bit,
@RegDate smalldatetime,
@CompanyID int OUTPUT
)
AS

BEGIN TRAN addmembertrans

INSERT INTO Company
(
CompanyName,
Address1,
Address2,
Address3,
Town_City,
Postcode,
Country,
TelNum,
FaxNum,
CompanyEmail,
WebSite,
RegDate

)

VALUES
(
@CompanyName,
@Address1,
@Address2,
@Address3,
@Town_City,
@Postcode,
@Country,
@TelNum,
@FaxNum,
@CompanyEmail,
@WebSite,
@RegDate
)

SELECT
@CompanyID = @@Identity

DECLARE @UserID int

INSERT INTO Users

(
Name,
Email,
Password,
CompanyID
)

VALUES
(
@Name,
@Email,
@Password,
@CompanyID
)

SELECT
@UserID = @@Identity

INSERT INTO UserRoles
(
UserID,
RoleID
)

VALUES
(
@UserID,
3
)

INSERT INTO UserRoles
(
UserID,
RoleID
)

VALUES
(
@UserID,
4
)


COMMIT TRAN addmembertrans

===========================================================

cheers,

si

 

[AWithers]

You have got nothing to check whether the first inserts work

try something like

IF @@ROWCOUNT = 0
rollback tran

at the relevant points

Andy

 

[rajeshkanna]

Hi ,

I think we should check error and rollback explicitly within your code after each and every insert/select/update/delete... statements.

just add the following segments after the insert/select/update/delete... statements in your procedure

if @@error <> 0
begin
rollback
return @@error
end

regards
Rajesh Kanna. N

 

[essa2000]

You can also use Error Handler to do so ; You will have to use If Cast(@@ERROR as int) > 0 goto ErrHandler after each insert or update then it will goto error handler and there will be rollback trans.



ALTER Procedure AddMember

(
@Name nvarchar(50),
@Email nvarchar(100),
@Password nvarchar(20),
@CompanyName nvarchar(50),
@Address1 nvarchar(100),
@Address2 nvarchar(100),
@Address3 nvarchar(100),
@Town_City nvarchar(50),
@Postcode nvarchar(10),
@Country nvarchar(50),
@TelNum nvarchar(15),
@FaxNum nvarchar(15),
@CompanyEmail nvarchar(100),
@WebSite nvarchar(100),
@Contact1 nvarchar(50),
@Position1 nvarchar(50),
@Email1 nvarchar(100),
@Tel1 nvarchar(15),
@Contact2 nvarchar(50),
@Position2 nvarchar(50),
@Email2 nvarchar(100),
@Tel2 nvarchar(15),
@Contact3 nvarchar(50),
@Position3 nvarchar(50),
@Email3 nvarchar(100),
@Tel3 nvarchar(15),
@MemberRates int,
@Payment int,
@ShowToAll bit,
@RegDate smalldatetime,
@CompanyID int OUTPUT
)
AS

BEGIN TRAN addmembertrans

INSERT INTO Company
(
CompanyName,
Address1,
Address2,
Address3,
Town_City,
Postcode,
Country,
TelNum,
FaxNum,
CompanyEmail,
WebSite,
RegDate

)

VALUES
(
@CompanyName,
@Address1,
@Address2,
@Address3,
@Town_City,
@Postcode,
@Country,
@TelNum,
@FaxNum,
@CompanyEmail,
@WebSite,
@RegDate
)


If Cast(@@ERROR as int) > 0 goto ErrHandler

SELECT
@CompanyID = Cast(@@Identity as int)

DECLARE @UserID int

INSERT INTO Users

(
Name,
Email,
Password,
CompanyID
)

VALUES
(
@Name,
@Email,
@Password,
@CompanyID
)

If Cast(@@ERROR as int) > 0 goto ErrHandler

SELECT
@UserID = @@Identity

INSERT INTO UserRoles
(
UserID,
RoleID
)

VALUES
(
@UserID,
3
)

If Cast(@@ERROR as int) > 0 goto ErrHandler

INSERT INTO UserRoles
(
UserID,
RoleID
)

VALUES
(
@UserID,
4
)

If Cast(@@ERROR as int) > 0 goto ErrHandler

COMMIT TRAN addmembertrans

Return 0

ErrHandler:

Rollback Transaction addmembertrans

Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[oddball]

This forum is great, thanks guys