How would you like to inherit...

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

General SQL Server Forums

New to SQL Server Programming

How would you like to inherit...

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-20 : 13:15:32

This...


SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT @TranCount = @@TRANCOUNT
	IF EXISTS
	(
		SELECT '' FROM CaseExpense
		WHERE CaseId=@CaseId AND ExpenseId=@ExpenseId
	)
	BEGIN

		IF @TranCount = 0 BEGIN TRANSACTION

through out a boat load of sprocs

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-01-20 : 13:37:51

I'm curious what the scenario is for the ROLLBACK

"If I started the transaction then rollback, otherwise return an error code and hope that the outer process does a rollback" ??

ROLLBACK to my SProc's own SAVEPOINT would be better me thinks ...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-20 : 13:39:26

relinquish

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-20 : 13:44:55

[code]
DECLARE @TranCount INT
DECLARE @ErrorCt INT
SET @ErrorCt=0
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT @TranCount = @@TRANCOUNT
IF EXISTS
(
SELECT '' FROM CaseExpense
WHERE CaseId=@CaseId AND ExpenseId=@ExpenseId
)
BEGIN

IF @TranCount = 0 BEGIN TRANSACTION
INSERT INTO CaseExpenseHistory
(ActionId, ExpenseId, CaseId, ExpenseDate,SecurityFirmId,HourlyRate, Cost, Description, IsPaid, IsActive, CreateId, CreateDt, UpdateId, UpdateDt)
SELECT 8 AS ActionId, ExpenseId, CaseId, ExpenseDate,SecurityFirmId,HourlyRate, Cost, Description, IsPaid, IsActive, CreateId, CreateDt, UpdateId, UpdateDt
FROM CaseExpense
WHERE CaseId=@CaseId AND ExpenseId=@ExpenseId
IF @@ERROR<>0
BEGIN
SET @ErrorCt=@ErrorCt+1
ROLLBACK TRANSACTION
RETURN
END
UPDATE CaseExpense
SET [Cost]=@ExpenseCost,Description=@Description,
ExpenseDate=@ExpenseDate,IsPaid=@IsPaid,
SecurityFirmId=@SecurityFirmId,HourlyRate=@HourlyRate,
UpdateId=@UserId, UpdateDt=GETDATE()
WHERE CaseId=@CaseId AND ExpenseId=@ExpenseId
IF @@ERROR<>0
BEGIN
SET @ErrorCt=@ErrorCt+1
ROLLBACK TRANSACTION
RETURN
END
END
ELSE
BEGIN
IF @TranCount = 0 BEGIN TRANSACTION
SELECT @ExpenseId= LastSeqNum FROM CaseSeqNum WITH (UPDLOCK) WHERE EntityId=10
SET @ExpenseId=@ExpenseId + 1
INSERT INTO [dbo].[CaseExpense]
(
[ExpenseId]
,[CaseId]
,[ExpenseDate]
,[HourlyRate]
,[SecurityFirmId]
,[Cost]
,[Description]
,[IsPaid]
,[CreateId]
)
VALUES
(
@ExpenseId
,@CaseId
,@ExpenseDate
,@HourlyRate
,@SecurityFirmId
,@ExpenseCost
,@Description
,@IsPaid
,@UserId
)
IF @@ERROR<>0
BEGIN
SET @ErrorCt=@ErrorCt+1
ROLLBACK TRANSACTION
RETURN
END
UPDATE CaseSeqNum
SET LastSeqNum=@ExpenseId
,Updatedt=GETDATE()
,UpdateId=@UserID
WHERE EntityId=10
IF @@ERROR<>0
BEGIN
SET @ErrorCt=@ErrorCt+1
ROLLBACK TRANSACTION
RETURN
END
END
IF @ErrorCt = 0
BEGIN
COMMIT TRANSACTION
SELECT @ExpenseId
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
END
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-01-21 : 04:15:39

Hmmm ... so only start a transaction if we are not already in one, but if we hit an error rollback the lot anyway ... so just BEGIN TRANSACTION would do and "I don't care about the transaction state of the caller"

ROFL @ usage of CaseSeqNum

Gawd forbid you ever get an error, all you get back is @ErrorCt=@ErrorCt+1 - no indication of which TRY-point in the Sproc it failed at ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources