April 10, 2010 at 5:05 am
CREATE PROCEDURE Delete_Template
(
@IDBIGINT,
@StatusBIT OUTPUT
)
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DELETEA
OUTPUT
deleted.col1, deleted.col2
INTOaudittable1(
col1, col2)
FROMmaintable1
WHEREID = @ID
IF @@ROWCOUNT > 0
BEGIN
DELETE A
OUTPUT
deleted.col1, deleted.col2
INTOaudittable2
(col1, col2)
FROMmaintable2 WHERE ID = @ID
END
COMMIT
SET @Status = 1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @Status = 0
ENDCATCH
END
this procecure created successfully but
When I execute the procedure
DECLARE @Status AS BIT
EXEC Delete_Template 12, @Status OUT
SELECT @Status
It is giving me error
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Can anyone suggest me how to remove this error or any other way to handle this...Please
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 10, 2010 at 6:13 am
Sorry I got the solution that was just a syntax error
I have not given alias name A in table...
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 11, 2010 at 1:11 am
Make sure you handle the CATCH block correctly too.
BEGIN TRY
BEGIN TRANSACTION;
SELECT 1/0;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
SELECT ERROR_MESSAGE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE();
END CATCH;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply