August 23, 2004 at 9:58 am
Hey all...
I'm a convert from Oracle PL\SQL and I know I'm missing something in my transaction control in this statement, but I can't seem to figure out what. I've played with placement of BEGIN and END statements around the IF @@ERROR <> 0 statements, but I can't seem to resolve this error.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing
I typically get a previous count that is one less than the current count.
My code is.
CREATE PROCEDURE sp_ORLInsertDefect
@ProjectNum int,
@Description varchar(100),
@Packager tinyint,
@Severity varchar(25),
@Testcase varchar(50),
@Detail varchar(8000)
AS
DECLARE @currTime datetime, @NewDefID int
SELECT @NewDefID = max(defect_id) FROM DEFECTS
SET @NewDefID = @NewDefID + 1
SET @currTime = GetDate()
BEGIN TRAN T1
INSERT INTO defects(defect_id, project_id, defect_sub_date, sev_no, procedure_type,
description, status, pkgr_id) VALUES (@NewDefID, @ProjectNum, @currTime, @Severity, @TestCase,
@Description, 'OPEN', @Packager)
IF @@ERROR <> 0
ROLLBACK TRANSACTION T1
SELECT 'An Unknown Error Occurred During the Edit Defect Stored Procedure'
RETURN(99)
INSERT INTO test_detail(defect_id, data) VALUES (@NewDefID, @Detail)
IF @@ERROR <> 0
ROLLBACK TRANSACTION T1
SELECT 'An Unknown Error Occurred During the Edit Defect Stored Procedure'
RETURN(99)
COMMIT TRAN T1
GO
Any help would be greatly appreciated. Thanks!!
August 23, 2004 at 12:42 pm
After IF @@ERROR 0 in each case you have multiple statements - you need to surround these by BEGIN and END. As it works just now if there are no errors you're transaction will run :
IF @@ERROR 0
ROLLBACK TRANSACTION T1 -- this doesn't run but execution jumps to outside now
-- so these two lines will run and the proc will return without rolling back.
SELECT 'An Unknown Error Occurred During the Edit Defect Stored Procedure'
RETURN(99)
Dave Hilditch.
August 27, 2004 at 8:16 am
SET @NewDefID = @NewDefID + 1 "
As and aside, you could set defect_id as an IDENTITY field with a Seed of 1 (the default) .
This will have the effect of automatically populating the defect_id with the next available number (and therefore you will not have to calculate or pass it).
Look at "Creating and Modifiying Identity Columns" and "Autonumbering and Indentifier Columns" in SQL Books Online.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply