Hi friends
I've written tow type
of code which do the same work, but I want to know which one is better? 1 or 2? Why?
I mean about Try & Catch.
Please Help me.
1.
ALTER Procedure [dbo].[Clerk_Insert]
@clk_Name nvarchar(80),
@clk_Family nvarchar(100),
@clk_FatherName nvarchar(80),
@clk_MeliCode char(10),
@clk_ShomarehShenasname char(10),
@clk_PersonnelNumber nvarchar(100)
As
Begin
Begin Try
Begin Tran a;
If exists(select clk_MeliCode, clk_Name, clk_Family, clk_FatherName, clk_PersonnelNumber
from Clerk
where (clk_MeliCode = @clk_MeliCode)
or (clk_Name= @clk_Name and clk_Family= @clk_Family and clk_FatherName = @clk_FatherName )or( clk_PersonnelNumber
=@clk_PersonnelNumber))
Begin
return -1;
End
else
begin
insert into Clerk
(clk_Name, clk_Family, clk_FatherName,clk_MeliCode, clk_ShomarehShenasname, clk_PersonnelNumber)
values
(@clk_Name, @clk_Family,@clk_FatherName,@clk_MeliCode,@clk_ShomarehShenasname, @clk_PersonnelNumber)
End
commit Tran a;
End Try
Begin Catch
Rollback Tran a;
select
ERROR_LINE() as
line, ERROR_MESSAGE() as messages;
End Catch
End
2.
ALTER Procedure [dbo].[Clerk_Insert]
@clk_Name nvarchar(80),
@clk_Family nvarchar(100),
@clk_FatherName nvarchar(80),
@clk_MeliCode char(10),
@clk_ShomarehShenasname char(10),
@clk_PersonnelNumber nvarchar(100)
As
Begin
If exists(select clk_MeliCode, clk_Name, clk_Family, clk_FatherName, clk_PersonnelNumber
from Clerk
where (clk_MeliCode = @clk_MeliCode)
or (clk_Name= @clk_Name and clk_Family= @clk_Family and clk_FatherName = @clk_FatherName )or( clk_PersonnelNumber
=@clk_PersonnelNumber))
Begin
return -1;
End
else
begin
insert into Clerk
(clk_Name, clk_Family, clk_FatherName,clk_MeliCode, clk_ShomarehShenasname, clk_PersonnelNumber)
values
(@clk_Name, @clk_Family,@clk_FatherName,@clk_MeliCode,@clk_ShomarehShenasname, @clk_PersonnelNumber)
End
End
Begin Try
Begin Tran a;
execute dbo.Clerk_Insert;
commit tran a;
End Try
Begin Catch
select
ERROR_NUMBER() as
Error_Numbers,
ERROR_LINE() as Error_Lines,
ERROR_MESSAGE() as
Error_Messages;
rollback tran a;
End Catch;
go
Sandeep Singh ShekhawatPosted May 22, 2013, 11:44 AM
My mean to say don't call procedure in itself using execute. like
execute dbo.Clerk_Insert;
It can create problem.
elham deljooeiPosted May 22, 2013, 1:09 AM
I've read your answer, but i have not understand this part properly, can you explain a little more?
For your try catch scenario your first approach is Okay because in second block you are using execute statement to recursive call same stored procedure so avoid extra execution cost
Jignesh TrivediPosted May 20, 2013, 12:11 AM
hi,
as per my knowledge, if you have multiple insert / update /Delete operation with in SQL server than you need have transaction to make sure your all data operation done correctly.
if you have single operation on data , so there is no need of transaction as well Try...catch block.
hope this will help you.
Sandeep Singh ShekhawatPosted May 19, 2013, 8:53 AM
All individual SQL Statements are automatically "In a Transaction" whether you explicitly say so or not. The only function that the "Begin Transaction", "Commit Transaction" and "RollBack Transaction" commands provide is to allow you to put two or more individual SQL statements into the same transaction.
You are using single insert statement so don't need explicit transaction.
For your try catch scenario your first approach is Okay because in second block you are using execute statement to recursive call same stored procedure so avoid extra execution cost