Issue i am facing is i have n number of tables in my database, i have created a procedure which takes parameters and perform insert and update transactions into the tables.
i have enclosed the query in transaction and will perform a rollback if error occurs in a try catch statments.
i have faced this issue randomly that in few tables there is no effect , for eg
create table tb1 (id varchar(100),data1 varchar(100))
create table tb2 (id varchar(100),data2 varchar(100))
create table tb3 (id varchar(100),data3 varchar(100))
create procedure sp_add
@data1 varchar(100),@data2 varchar(100),@data3 varchar(100)
as
BEGIN TRANSACTION
BEGIN TRY
declare @count int,@uniqueId varchar(100)
select @count=count(*) from tb1
if(@count is null)
set @count=0
set @count=@count+1
set @uniqueId='Unq'+cast(@count as varchar(100))
insert into tb1 values (@uniqueId,@data1)
insert into tb2 values (@uniqueId,@data2)
insert into tb3 values (@uniqueId,@data3)
COMMIT TRANSACTION
SELECT 't'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_MESSAGE()
END CATCH
GO
My procedure is something like that with lots of select / insert and update statements , most of the time it will work fine but something it may happen to skip inserting or updating the records in a table.
Any solution for my problem will be much appreciated.
Thank you.