January 25, 2003 at 3:48 am
Hi,
Consider the following stored procedure
I am calling this stored procedure from a visual basic Application.
This procedure is just meant for inserting values in three tables
depends on the @BankType and @BankBranchType variables.
Lets just see the first case in which @BankType and @BankBranchType=0
Just ignore the other ones.
I have also used SET XACT_ABORT ON so that if error occured at any statement
all previous successfull transaction will also be rollbacked. This is compulsory.
There are four insert statements after BEGIN TRAN for the First case
The problem is that when primary key violation or any other error is occured
The error notification is only sent to client application if error occured on
the "FIRST" insert statement after BEGIN TRAN
but if error occured on the "SECOND" or "THIRD" insert statment it does not
send any notification to client application, Although the transaction is rollbacked
due to SET XACT_ABORT ON.
Please tell me why its behaving like this, any logic?
CREATE PROCEDURE [dbo].[sp_addaccounts]
@BankType tinyint,@BankBranchType tinyint,@CurType tinyint,
@bank_code tinyint,@bank_desc varchar(50),
@bank_branch_code tinyint,@bank_branch_desc varchar(255),
@accountsrlno tinyint,@currency_code tinyint,@accountno varchar(35),@accountTitle varchar(50),
@currency_desc varchar(15) as
SET XACT_ABORT ON
if (@BankType=0 and @BankBranchType=0) -- New Bank,New BankBranch
begin
begin tran
insert into codes_bank --FIRST INSERT
(bank_code,bank_desc)
values(
@bank_code,@bank_desc
)
--The notification is only sent to Client Application if error statement is
--the first statement after BEGIN TRAN
insert into codes_bank_branch --SECOND INSERT
(bank_code,bank_branch_code,bank_branch_desc)
values(
@bank_code,@bank_branch_code,@bank_branch_desc
)
-- if error occured on 2nd or any other no notification is sent to
Client Application.
insert into Codes_account --THIRD INSERT
(accountSrlNo,bank_code,bank_branch_code,currency_code,accountNo,accountTitle)
values(
@accountSrlNo,@bank_code,@bank_branch_code,
@currency_code,@accountno,@accountTitle
)
if (@CurType=0)
insert into Codes_Currency --FOURTH INSERT
(currency_code,currency_desc)
values(
@Currency_Code,@Currency_Desc
)
commit tran
end
else if (@BankType=1 and @BankBranchType=0) -- New Bank,Existing Bank Branch
begin
begin tran
insert into codes_bank_branch
(bank_code,bank_branch_code,bank_branch_desc)
values(
@bank_code,@bank_branch_code,@bank_branch_desc
)
insert into Codes_account
(accountSrlNo,bank_code,bank_branch_code,currency_code,accountNo,accountTitle)
values(
@accountSrlNo,@bank_code,@bank_branch_code,
@currency_code,@accountno,@accountTitle
)
if (@CurType=0)
insert into Codes_Currency
(currency_code,currency_desc)
values(
@Currency_Code,@Currency_Desc
)
commit tran
end
else if (@BankType=1 and @BankBranchType=1) --Existing Bank, Existing Bank Branch
begin
begin tran
insert into Codes_account
(accountSrlNo,bank_code,bank_branch_code,currency_code,accountNo,accountTitle)
values(
@accountSrlNo,@bank_code,@bank_branch_code,
@currency_code,@accountno,@accountTitle
)
if (@CurType=0)
insert into Codes_Currency
(currency_code,currency_desc)
values(
@Currency_Code,@Currency_Desc
)
commit tran
end
GO
Shahzad Hassan
Software Engineer
NADRA (National Database & Registration Authority)
Islamabad, Pakistan
January 26, 2003 at 4:47 am
I know this is not what you are looking for....
I prefer to use errorhandler to solve this and it works for me always as in errorhandler i am raising the error with custom message and rollback the transaction...
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
January 26, 2003 at 5:20 pm
try putting
set nocount on
at the top of the SP.
Cursors never.
DTS - only when needed and never to control.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply