December 21, 2009 at 4:56 am
hi every one,
i am using the below sp for updating three tables at a time with try and catch block. is there any performance issues by using this or any possible errors.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].test
(@name VARCHAR(40)
,@name1 varchar(50)
)
AS
BEGIN
SET NOCOUNT ON
create table #temp(id int not null identity(1,1),primary key, valuestostore varchar(50))
insert into #temp (valuestostore )select perval from users where
name=@name;
BEGIN TRY
BEGIN TRANSACTION
UPDATE emp
SET vcname=@name1
WHERE valname in (select valuestostore from #temp);
UPDATE dept
SET vcname=@name1
WHERE valname in (select valuestostore from #temp);
UPDATE sal
SET vcname=@name1
WHERE valname in (select valuestostore from #temp);
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT >0
-- SELECT ERROR_NUMBER
ROLLBACK
END CATCH
DROP TABLE #TEMP
END
December 21, 2009 at 7:04 am
No, that's appropriate. However I would include the rollback in the preceeding "if" block like this:
IF @@TRANCOUNT > 0
begin
-- error handling
ROLLBACK -- no rollback if @@trancount = 0
end
The probability of survival is inversely proportional to the angle of arrival.
December 21, 2009 at 7:14 am
hi sturner,
Thank you. one more thing,What happens when first update statement succeed,second one fails and thrid one succeed?
Is there any major difference between @@transcount and xact_state(). it seems both are same,can we use xact_state() in above scenario or @@transcount will be better....
Thanks
Rock..
December 21, 2009 at 9:32 am
I believe for your purposes here they are equivalent, however it is better to use xact_state() for reasons I won't elaborate on.
It doesn't matter which one or how many of the updates fail, the processing will end up in the catch block for you to roll back. Of course, if you needed to know which one failed you could set a flag or something after each update, then it may be possible (depending upon the error) for you to take alternate action and ultimately commit the transaction.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply