Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

transactio error in stored procedure

kapiloo7kapiloo7 Member Posts: 45
I have following table structure and a stored procedure for this table
But the Stored Procedure execution genrates error.

table :- t_Condition
[code]
PK Condition_ID UDT_ID (int) 4 0
Age_From UDT_Age (tinyint) 1 0
Age_To UDT_Age (tinyint) 1 0
Pos_From UDT_CDV_Code (varchar) 10 0
Pos_To UDT_CDV_Code (varchar) 10 0
Property_Ind UDT_CDV_Code (varchar) 10 0
Updt_By UDT_Last_Updt_By (int) 4 0
Updt_Dt_Tm UDT_Last_Updt_Dt (datetime) 8 0
[/code]

the stored procedure is as follows

[code]
CREATE PROCEDURE s_Condition_Delete
@Condition_ID UDT_ID
AS

SET NOCOUNT ON

BEGIN TRANSACTION
DELETE FROM t_Condition
WHERE Condition_ID = @Condition_ID
RETURN 0
COMMIT TRANSACTION

@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN 1
END

SET NOCOUNT OFF

GO
[/code]
when executed this stored procedure it gives following error.


Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

can any body pl help me?

Comments

  • ShanooShanoo Member Posts: 32
    The only problem with the code is that you are using "RETURN 0" statement before "COMMIT TRANSACTION" statement in the following block:

    : BEGIN TRANSACTION
    : DELETE FROM t_Condition
    : WHERE Condition_ID = @Condition_ID
    : RETURN 0
    : COMMIT TRANSACTION

    The Return statement will cause the control to exit from the procedure before calling the COMMIT TRANSACTION statement, and hence the Transaction count will not be reset to 0 which willthrow the error. You can avoid this error by moving "RETURN 0" statement after "COMMIT TRANSACTION".

    Also, if you have only one statement ("DELETE FROM t_Condition WHERE Condition_ID = @Condition_ID" in our case) within the transaction, you do not need to start an explicit transaction even if the statement affects multiple records, because a single statement in itself is atomic in SQL Server.

    - HTH
Sign In or Register to comment.