Transaction in stored procedure

Posted by Modit under Sql Server on 4/26/2016 | Points: 10 | Views : 351 | Status : [Member] | Replies : 3
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.




Responses

Posted by: Poster on: 4/26/2016 [Member] Starter | Points: 25

Up
0
Down
I think your stored procedure is just fine. How frequently you face this issue, I do not think that issue should be due to stored procedure. See the series of events before it fails to insert into all three tables. If your stored procedure is called it must insert into either all three tables or none of them.

Thanks

Modit, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Modit on: 4/26/2016 [Member] Starter | Points: 25

Up
0
Down
First it was on random basis, i thought maybe it would be due to many users accessing the procedure simultaneously , but yesterday it happened for more than 2 times and there was just a single user accessing it.

Modit, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Professionaluser on: 4/27/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Check for triggers if you have on any of the tables involved in the transaction.... why because there is a possibility to face this kind of data missing/corruption scenario with triggers...

1. Trace out the issue by creating trace using SQL Profiler if possible
2. re-verify the logic of SP
3. are you facing any errors in the case of missing insert/updates??

refer below link for understanding error handling approaches...
https://www.simple-talk.com/sql/t-sql-programming/defensive-error-handling/

Modit, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response