Click here to Skip to main content
14,930,311 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
ALTER PROCEDURE [dbo].[InsertData1]
(
@Operation varchar(1),
--other parameters---)
AS
BEGIN TRAN
SET NOCOUNT off;

if @Operation = 'A'
begin
INSERT INTO table1 (---) values (--)
INSERT into table2(--)values(--)
IF (@@ERROR <> 0) GOTO ERR_HANDLERUpdate
end

else if @Operation = 'B'
begin

--- another update query----
IF EXISTS(Select * from Med Where a=@a)
begin
Delete from Med where a=@a
end

INSERT into Med(--)values(--)
IF (@@ERROR <> 0) GOTO ERR_HANDLERUpdate
end

COMMIT TRAN
RETURN 1

ERR_HANDLERUpdate:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 0

I want to return 0 if any error occurs and 1 if all querries executes successfully without any error.But the procedure is returning count of no of queries executed in procedure.
How to check if procedure is executes successfully and return value..Help Me..
Posted
Updated 8-Feb-14 1:32am
v3

1 solution

Define an output parameter and pass value to that parameter as you wish

like :

SQL
ALTER PROCEDURE [dbo].[InsertData1]
(
@Operation varchar(1),
@Result int output,
--other parameters---)
AS
.
.
.

COMMIT TRAN
Set @Result = 1;
 
ERR_HANDLERUpdate:
PRINT 'Unexpected error occurred!'
Set @Result = 0;
ROLLBACK TRAN


let me know if this is useful :)

Updates : If you don't want to return No of rows affected by query/queries
use : Set NoCount On
I updated only what is required to resolve your error... All other code should remain same as your original. Please check again...

Don't forget to accept as answer if it helps you.
   
v4
Comments
pwavell 8-Feb-14 9:25am
   
it gives me error "the rollback transaction request has no corresponding begin transaction".Am i Missing something?
CHill60 8-Feb-14 9:36am
   
What code did you put between the "AS" and the "COMMIT TRAN"? You had the BEGIN TRAN in your post

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900