SPROCS Multi inserts - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SPROCS Multi inserts

Author

Topic

tech1
Starting Member

49 Posts

Posted - 2010-06-10 : 16:48:55

I am using a SPROC to do 3 inserts into different tables.

a couple of the tables require an FK parameter. that FK is the PK generated when a row is inserted into the related table.

how can I get the ID that has been generated for that current inserting table?

sure, SCOPE_IDENTITY works but that is only for the current transaction/session, not for every inserts you do in that session/sproc execution.

INSERT STATEMENT 1 (I need ID generated)
INSERT STATEMENT 2 (I need ID parameter from ID STATEMENT 1)
INSERT Statement 3 (I need ID parameter from ID STATEMENT 2)

I dont want to have to do a SELECT [ID] FROM [table] WHERE <conditions> as that would be expensive just to get the ID(???)

or would it be better having them seperated into each sprocs then have 1 sproc calling each of them, with each sproc returning back an ID as an output param using SCOPE_IDENTITY() ?

Kristen
Test

22859 Posts

Posted - 2010-06-10 : 17:34:38

[code]INSERT STATEMENT 1
SELECT @MyID = SCOPE_IDENTITY()
INSERT STATEMENT 2 (... @MyID ..)
SELECT @MyID = SCOPE_IDENTITY()
INSERT Statement 3 (... @MyID ..)
[/code]
or am I missing something?

tech1
Starting Member

49 Posts

Posted - 2010-06-10 : 19:30:40

doh. I was doing it before the insert statement..... stupid me. its late!

question:

I want to doing transactions so if something is wrong in the insertion proceedings at any point in this sproc, I want to do a rollback.

this is fine except, since I now have 2 SPROCS which also do a begin transaction and a rollback and a raiserror, the caller also has a begin transaction, rollback and raises an error

but when this happens (when the external sproc raises an error) it does do a rollback correctly but I get an error like this:

Msg 50000, Level 16, State 1, Procedure InsertUser, Line 20
test error
Msg 266, Level 16, State 2, Procedure InsertUser, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.
Msg 50000, Level 16, State 1, Procedure CreateForumUser, Line 47
Error in inserting into table users

tech1
Starting Member

49 Posts

Posted - 2010-06-10 : 19:33:04

To make it clear:

quote:

CREATE PROCEDURE [dbo].[InsertUser] (

@firstname nvarchar(50),
@lastname nvarchar(50)

)

AS
BEGIN TRANSACTION

SET NOCOUNT ON;

RAISERROR('test error', 16, 1)
RETURN

COMMIT

main sproc:

quote:

BEGIN TRANSACTION

SET NOCOUNT ON;
-- check if user exists

DECLARE @userExists bit
EXEC [CheckUsernameExists] @username, @userExists output
IF (@userExists = 1)
SET @result = 2 --user exists
ELSE
BEGIN
-- username is fine.
-- first insert the basic user details
DECLARE @userID int
EXEC [InsertUser] @firstname, @lastname, @userID output
--SELECT @userID

IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR('Error in inserting into table users', 16, 1)
RETURN
END
ELSE
--BLAH
-- BLAH BLAH BLAH
COMMIT

Kristen
Test

22859 Posts

Posted - 2010-06-10 : 19:59:40

You need to use a SAVEPOINT in your "nested" Sprocs; you can rollback to that without resetting the Transaction Count all the way back to zero

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37247#112267

tech1
Starting Member

49 Posts

Posted - 2010-06-10 : 20:24:01

Thanks! Almost there I think.

I've taken out the begin/commit trans in the external sprocs so now im dealing with it within the main sproc, but still a little issue.

This is what the main sproc now looks like

quote:

BEGIN TRANSACTION InsertFullForumUserDetails

-- check if user exists

DECLARE @userExists bit
EXEC [CheckUsernameExists] @username, @userExists output
IF (@userExists = 1)
SET @result = 2 --user exists
ELSE
BEGIN
BEGIN TRAN InsertBasicUser
SAVE TRAN InsertBasicUser
-- username is fine.
-- first insert the basic user details
DECLARE @userID int
EXEC [InsertUser] @firstname, @lastname, @userID output
--SELECT @userID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN InsertBasicUser

--RAISERROR('Error in inserting into table users', 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRAN InsertBasicUser
BEGIN TRAN InsertForumUserDetails
SAVE TRAN InsertForumUserDetails
DECLARE @forumUserID int
EXEC [InsertForumUser] @userID, @username, @encryptedPass, @activationKey, @forumUserID output
--SELECT @forumUserID
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN InsertForumUserDetails
RAISERROR('Error inserting forum user settings', 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRAN InsertForumUserDetails
SAVE TRAN InsertFullForumUserDetails
DECLARE @cultureInfoID int
EXEC [GetCultureInfoByCountryIDAndCultureInfoID] @countryID, @cultureID, @cultureInfoID output

-- ok, now its into usersettings
INSERT INTO UserSettings
([ForumUserID], [CultureInfoID], [AvatarImageLink], [SecurityQuestion], [SecurityAnswer], [EmailAddress])
VALUES (@forumUserID, @cultureInfoID, @avatarImageLink, @securityQuestion, @securityAnswer, @emailAddress)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN InsertFullForumUserDetails
RAISERROR('Error inserting into UserSettings', 16, 1)
RETURN
END
END
END
END

COMMIT TRAN InsertFullForumUserDetails

SET @result = 1 -- success

Kristen
Test

22859 Posts

Posted - 2010-06-11 : 03:01:38

Your BEGIN TRANSACTION and SAVE TRANSACTION need to have different names


BEGIN Label1
SAVE Label2

IF SomeError ROLLBACK Label2
COMMIT Label1

thus you always finish the Sproc with a COMMIT to Label1 - i.e. the Transaction Count at which you arrived in the Sproc

tech1
Starting Member

49 Posts

Posted - 2010-06-11 : 05:42:11

Thanks.

say for example down the line there happens to be an error but we committed Label1. lets say along the way we commit Label1 a few times but hits a point where there is an error. we do a rollback on label2 - would it then not commit/uncommit the previous commits of label1?

tech1
Starting Member

49 Posts

Posted - 2010-06-11 : 08:25:55

:( no luck. im missing something somewhere.

quote:

BEGIN TRANSACTION InsertFullForumUserDetails

-- check if user exists

DECLARE @userExists bit
EXEC [CheckUsernameExists] @username, @userExists output
IF (@userExists = 1)
SET @result = 2 --user exists
ELSE
BEGIN
IF EXISTS(SELECT [ID] FROM UserSettings WHERE [EmailAddress] = @emailAddress)
SET @result = 4
ELSE
BEGIN
BEGIN TRAN InsertBasicUser
SAVE TRAN InsertBasicUser2
-- username is fine.
-- first insert the basic user details
DECLARE @userID int
EXEC [InsertUser] @firstname, @lastname, @userID output
--SELECT @userID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN InsertBasicUser2
SET @result = 8
--RAISERROR('Error in inserting into table users', 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRAN InsertFullForumUserDetails
BEGIN TRAN InsertForumUserDetails
SAVE TRAN InsertForumUserDetails2
DECLARE @forumUserID int
EXEC [InsertForumUser] @userID, @username, @encryptedPass, @activationKey, @forumUserID output
--SELECT @forumUserID
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN InsertForumUserDetails2
SET @result = 8
--RAISERROR('Error inserting forum user settings', 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRAN InsertFullForumUserDetails
DECLARE @cultureInfoID int
EXEC [GetCultureInfoByCountryIDAndCultureInfoID] @countryID, @cultureID, @cultureInfoID output

SAVE TRAN InsertFullForumUserDetails2
-- ok, now its into usersettings
INSERT INTO UserSettings
([ForumUserID], [CultureInfoID], [AvatarImageLink], [SecurityQuestion], [SecurityAnswer], [EmailAddress])
VALUES (@forumUserID, @cultureInfoID, @avatarImageLink, @securityQuestion, @securityAnswer, @emailAddress)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN InsertFullForumUserDetails2
SET @result = 8
--RAISERROR('Error inserting into UserSettings', 16, 1)
RETURN
END
ELSE
SET @result = 1 -- success
END
END
END
END

COMMIT TRAN InsertFullForumUserDetails

Kristen
Test

22859 Posts

Posted - 2010-06-11 : 12:48:56

[code]
...
COMMIT TRAN InsertFullForumUserDetails
DECLARE @cultureInfoID int
EXEC [GetCultureInfoByCountryIDAndCultureInfoID] @countryID, @cultureID, @cultureInfoID output

SAVE TRAN InsertFullForumUserDetails2
-- ok, now its into usersettings
INSERT INTO UserSettings
...
[/code]
lacks a wrapper BEGIN TRANS and duplicates a name used earlier, dunno if SQL minds that, but it will be confusing I expect.

"lets say along the way we commit Label1 a few times but hits a point where there is an error. we do a rollback on label2 - would it then not commit/uncommit the previous commits of label1?"

So you have, say, MySProcOuter which calls MySProcInner in a loop (lots of time)

and MySProcInner does
[code]
BEGIN TRANSACTION MySProcInner_01
SAVE TRANSACTION MySProcInner_02
...
IF @intMyError ROLLBACK MySProcInner_02
COMMIT MySProcInner_01
[/code]
and MySProcOuter loops round and calls MySProcInner 50 times, say, and each time is successful and MySProcInner does COMMIT MySProcInner_01 each time, without a rollback.

and then the next, 51st, time that MySProcInner is called it find a problem in the data and does the ROLLBACK MySProcInner_02 (immediately followed by COMMIT MySProcInner_01) then:

The data changed in MySProcInner on the 51st time is rolled back
but the data changed by MySProcInner previous from 1st to 50th time is kept.

However, if MySProcOuter then decides to do a ROLLBACK then ALL the data changed by MySProcInner is rolled back.

tech1
Starting Member

49 Posts

Posted - 2010-06-11 : 20:26:47

you say:

quote:
lacks a wrapper BEGIN TRANS and duplicates a name used earlier, dunno if SQL minds that, but it will be confusing I expect.

where exactly does it lack a wrapper in that snippet you quoted?

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 02:30:20

The bit that I highlighted in red!

tech1
Starting Member

49 Posts

Posted - 2010-06-12 : 10:22:04

tried that but still no go :-/

quote:

Msg 50000, Level 16, State 1, Procedure InsertUser, Line 21
test error
Msg 266, Level 16, State 2, Procedure InsertUser, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 17, current count = 18.
Msg 266, Level 16, State 2, Procedure CreateForumUser, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 15, current count = 18.

(1 row(s) affected)

COMMIT TRAN InsertFullForumUserDetails
DECLARE @cultureInfoID int
EXEC [GetCultureInfoByCountryIDAndCultureInfoID] @countryID, @cultureID, @cultureInfoID output
BEGIN TRAN test
SAVE TRAN InsertFullForumUserDetails2
SAVE TRAN test
-- ok, now its into usersettings
INSERT INTO UserSettings
([ForumUserID], [CultureInfoID], [AvatarImageLink], [SecurityQuestion], [SecurityAnswer], [EmailAddress])
VALUES (@forumUserID, @cultureInfoID, @avatarImageLink, @securityQuestion, @securityAnswer, @emailAddress)

Kristen
Test

22859 Posts

Posted - 2010-06-13 : 05:34:29

"BEGIN TRAN test
SAVE TRAN InsertFullForumUserDetails2
SAVE TRAN test"

I'm not understanding something here - why have you got two SAVE TRAN? and the names duplciate between BEING TRAN test and SAVE TRAN test - I don;t know if that matters, but I think it would be better to use unambiguous names

What you need is


BEGIN TRANS Label1
SAVE TRANS Label2

...

IF @intError ROLLBACK TRANS Label2
COMMIT TRANS Label1 -- Do this both for error, and for no error

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources