stored procedure with transactions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

stored procedure with transactions

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-05-26 : 18:33:22

I am working on a website that inserts members into the database after tehy have filled out an application. With the current database design the "TBLQUESTIONS" table is separate from the "TBLUSERDETAILS" table. What I need to do is after inserting the first row into "TBLUSERDETAILS" I need to get the value of the Identity Column (userID) and use that value to continue with the 6 inserts into the "TBLQUESTIONS" table. How can I do this?? I have included the stored procedure below. Any insight / critisism is greatly appreciated. Thanks alot!

CREATE PROCEDURE [insert_new]
(
@NameOnline [varchar](15),
@NameFirst [varchar](20) = NULL,
@NameLast [varchar](20) = NULL,
@Password [varchar](20),
@Address [varchar](50) = NULL,
@City [varchar](25) = NULL,
@PhoneNumber [varchar](20) = NULL,
@StateProvID [tinyint],
@CountryID [tinyint],
@PostalCode [varchar](7) = NULL,
@EmailAddress [varchar](50),
@GenderID [tinyint],
@BodyTypeID [tinyint],
@EyeColorID [tinyint],
@HairColorID [tinyint],
@Age [tinyint],
@SexualityID [tinyint],
@HeightFeet [tinyint],
@HeightInches [tinyint],
@EmploymentID [tinyint],
@EducationID [tinyint],
@SmokeID [tinyint],
@DrinkID [tinyint],
@StatusID [tinyint],
@response1 [varchar](200) = NULL,
@response2 [varchar](200) = NULL,
@response3 [varchar](200) = NULL,
@response4 [varchar](200) = NULL,
@response5 [varchar](200) = NULL,
@response6 [varchar](200) = NULL,
@Date [smalldatetime],
@Active [tinyint],
@UserNote [varchar](35) = NULL,
@IP [varchar](15))
AS
SET NOCOUNT ON
--Start a transaction
BEGIN TRANSACTION
--First Action
INSERT INTO [tblUserDetails]
(
[NameOnline],
[NameFirst],
[NameLast],
[Password],
[Address],
[City],
[PhoneNumber],
[StateProvID],
[CountryID],
[PostalCode],
[EmailAddress],
[GenderID],
[BodyTypeID],
[EyeColorID],
[HairColorID],
[Age],
[SexualityID],
[HeightFeet],
[HeightInches],
[EmploymentID],
[EducationID],
[SmokeID],
[DrinkID],
[StatusID],
[Date],
[Active],
[UserNote],
[IP],
[Forum_Level],
[Forum_Posts])

VALUES
(
@NameOnline,
@NameFirst,
@NameLast,
@Password,
@Address,
@City,
@PhoneNumber,
@StateProvID,
@CountryID,
@PostalCode,
@EmailAddress,
@GenderID,
@BodyTypeID,
@EyeColorID,
@HairColorID,
@Age,
@SexualityID,
@HeightFeet,
@HeightInches,
@EmploymentID,
@EducationID,
@SmokeID,
@DrinkID,
@StatusID,
@Date,
@Active,
@UserNote,
@IP,
'1',
'0' )
--Some checking
IF NOT @@ROWCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not Insert New Member tblUserDetails',16,1)
RETURN(@@ERROR)
END
--Second Action

--Question 1
INSERT INTO [tblQuestions]
( [userID],
[questionID],
[response])

VALUES
(@userID,
1,
@response1)
--Question 2
INSERT INTO [tblQuestions]
( [userID],
[questionID],
[response])

VALUES
(@userID,
2,
@response2)
--Question 3
INSERT INTO [tblQuestions]
( [userID],
[questionID],
[response])

VALUES
(@userID,
3,
@response3)
--Question 4
INSERT INTO [tblQuestions]
( [userID],
[questionID],
[response])

VALUES
(@userID,
4,
@response4)
--Question 5
INSERT INTO [tblQuestions]
( [userID],
[questionID],
[response])

VALUES
(@userID,
5,
@response5)
--Question 6
INSERT INTO [tblQuestions]
( [userID],
[questionID],
[response])

VALUES
(@userID,
6,
@response6)

GO

--Some checking
IF NOT @@ROWCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not Insert Questions',16,1)
RETURN(@@ERROR)
END
ELSE COMMIT TRANSACTION

GO

Edited by - mike123 on 05/28/2002 17:06:07

nr
SQLTeam MVY

12543 Posts

Posted - 2002-05-26 : 20:37:41

after each of the inserts you need something like

if @@error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end

just putting it at the end will lose the errors.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-05-26 : 20:55:20

nr, this is all a bit over my head.. how important is it to put each t-sql statement in a transaction?

thanks

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-05-26 : 21:01:47

What I tend to do is have 2 local variables

Declare @err int
Declare @cnt int

Then after each modification statement have

select @err=@@ERROR,@cnt=@@ROWCOUNT

If @err<>0
begin
RAISERROR('Error message Text',16,1)
IF @@TRANCOUNT >0 ROLLBACK TRAN
return 1
end

--If we are expecting rows to be affected
--Can also be used to make sure only one row
--is affected by changing to If @cnt<>1
If @cnt=0
begin
RAISERROR('No rows affected',16,1)
IF @@TRANCOUNT >0 ROLLBACK TRAN
return 1
end

Remember that any statement will reset @@ERROR and @@ROWCOUNT
This includes PRINT statements and IF blocks even if they evaluate
false - you might think that an IF statement that evaluates false and thus isn't executed would not affect @@ROWCOUNT but it does e.g.

select * from master..sysdatabases

if @@ERROR<>0 raiserror('Error',16,1) --False (hopefully !!)

select @@ROWCOUNT --returns 0 because of the IF statement

HTH
Jasper Smith

Edited by - jasper_smith on 05/26/2002 21:03:44

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-05-26 : 23:27:20

thank you, I'll have to do a bit more reading before I get this one!

mike

nr
SQLTeam MVY

12543 Posts

Posted - 2002-05-27 : 09:23:06

The transaction will prevent any statement from being commetted until the commit statement is executed. It will also hold locks on updated entities for the duration to sto them being accesed by other spids.
Therefor you need a single transaction to enclose all statements.
After each statement you will need to check the error code - if it fails perform a rollback and exit. If everything succeeds then preform the commit.

begin tran
insert ...
if @@error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
insert ...
if @@error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
commit tran

could haver a single error exit - handy if you want to add extra error processing.

declare @msg varchar(100), @error int
begin tran
insert ...
select @error = @@error
if @error <> 0
begin
set @msg = 'failed insert 1'
goto TranErrExit
end
insert ...
select @error = @@error
if @error <> 0
begin
set @msg = 'failed insert 2'
goto TranErrExit
end
commit tran
return
TranErrExit:
raiserror(@msg,16,-1)
rollback tran
return

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-05-28 : 17:04:01

Thank you,

1 last thing if I may

My second transaction to my last transaction must insert the IDENDITY column of the first INSERT. How can I achieve this? I had this in 2 seperate SP's before but it was causing some integrity problems I believe.

Thanks again,
Mike

nr
SQLTeam MVY

12543 Posts

Posted - 2002-05-29 : 12:12:31

You only have one transaction - I think yuo mean second o last insert.

insert tbl ...
if @@error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end

insert tbl2 (tbl_id)
select @@identity
if @@error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end

You might prefer to save the identity in case someone adds some code which loses it.

declare @error int, @id int

insert tbl ...
select @error = @@error, @id = @@identity
if @error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end

insert tbl2 (tbl_id)
select @id
select @error = @@error
if @error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end

note that @error is still set in the first statement following the insert.

select @id = @@identity
select @error = @@error

would not work as @@error would be cleared by setting @id.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources