How to roll back if 0 records are inserted

Question

0.00/5 (No votes)

See more:

So i want to
a. insert into table1
b. insert into table2
c. Update table3

if 0 records are inserted into table2, i don't want the insert into table1 to occur

also the three steps should be completed together successfully

What I have tried:

create PROCEDURE [dbo].[sp]
	@Id1 int
	,@BBId int
        ,@Text nvarchar(100) =''
AS
Begin
  Begin TRY
	Begin transaction
	        declare @Id int
            
		INSERT INTO table1 
		select @Text where @Text <> ''

		SET @Id = SCOPE_IDENTITY()

		INSERT INTO table2 (Id1, @Id)
			select @Id1,@Id from table3
			where table3.BBId = @BBId AND table3.Flag= 0 and 
                        table3.Id1 = @Id1
		
		        UPDATE    table3
			SET       Flag= 1
			WHERE   
				 table3.BBId = @BBID 
				AND table3.Id1 = @Id1
	COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  ROLLBACK; 
  throw
  END CATCH  
END
Return 0

Posted 3-Oct-20 10:08am

Member 14800672

Updated 7-Oct-20 5:14am

v4

Add a Solution

Comments

Sandeep Mewara 3-Oct-20 18:02pm

if 0 records are inserted into table2 => keeping error case aside, wouldn't this be just when where clause do not bring any data?

INSERT INTO table2 (Id1, @Id)
			select @Id1,@Id from table3
			where table3.BBId = @BBId AND table3.Flag= 0 and 
                        table3.Id1 = @Id1

Above SQL where clause has no dependency on table1, why wait for table 1 insertion? Right?

Member 14800672 4-Oct-20 1:38am

It depends on table1... I take @Id from table1 and use it in table2... So if 0 records are inserted in table2 i dont want the insert to happen in table1

Sandeep Mewara 4-Oct-20 3:25am

Under what circumstance you see that insert in table 2 would fail?
Only if smething foes wrong, and if so your try-catch would trigger in.

So, only valid way how nothing gets inserted is no record selected for insert in table2 which is independent of first step. I can provide sample sql of asked flow and sequence but I don't see why you need to follow this sequence to decide commit/rollback

Member 14800672 4-Oct-20 3:56am

insert on table2 wouldn't fail as an error, but 0 records are inserted. Maybe @Id1 is wrong... So if @Id1 is wrong, it will insert into table1, it won't insert into table2 or update table3. So no errors here my catch won't take anything. But What i want is that if i don't insert anything into table2 then i don't want the insert into table1 to happen at all

Sandeep Mewara 4-Oct-20 7:21am

What kind of constraint you have in table2 that would lead to not insertion of record because of @Id1.

When you say nothing inserted, wouldn't the query throw an error if for any reason it does not insert? I am still not clear on when there will be no error but 0 records inserted in table 2. (other then when where clause brings in 0 records which is independent of table 1)

ZurdoDev 5-Oct-20 13:35pm

Just check @@ROWCOUNT and if it's 0, rollback.

Member 14800672 10-Oct-20 7:47am

I did this

create PROCEDURE [dbo].[sp]
@Id1 int
,@BBId int
,@Text nvarchar(100) =''
AS
Begin
Begin transaction
declare @Id int

INSERT INTO table1
select @Text where @Text <> ''

SET @Id = SCOPE_IDENTITY()

INSERT INTO table2 (Id1, @Id)
select @Id1,@Id from table3
where table3.BBId = @BBId AND table3.Flag= 0 and
table3.Id1 = @Id1

IF(@@ROWCOUNT <= 0)
BEGIN

rollback transaction;
return
END
else
begin
UPDATE table3
SET Flag= 1
WHERE
table3.BBId = @BBID
AND table3.Id1 = @Id1
end
COMMIT TRANSACTION
END
Return 0

when i run it in SQL i get no errors, however when i call the stored procedure from .Net i get this error:
"Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.\r\nTransaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0."

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Answer 1 · 2020-10-07T05:15:00

Solution 2

Am I missing something, or is it as easy as:

1) keep the error handling ROLLBACK
2) After successfully executing your inserts and updates, check that they are actually in the table. If any of them fail to reflect the added and updated records, execute the ROLLBACK.

Possibly not the most efficient route, but it is simple enough.

Posted 7-Oct-20 5:15am

W∴ Balboos, GHB

Comments

Member 14800672 10-Oct-20 7:46am

I did this

create PROCEDURE [dbo].[sp]
@Id1 int
,@BBId int
,@Text nvarchar(100) =''
AS
Begin
Begin transaction
declare @Id int

INSERT INTO table1
select @Text where @Text <> ''

SET @Id = SCOPE_IDENTITY()

INSERT INTO table2 (Id1, @Id)
select @Id1,@Id from table3
where table3.BBId = @BBId AND table3.Flag= 0 and
table3.Id1 = @Id1

IF(@@ROWCOUNT <= 0)
BEGIN

rollback transaction;
return
END
else
begin
UPDATE table3
SET Flag= 1
WHERE
table3.BBId = @BBID
AND table3.Id1 = @Id1
end
COMMIT TRANSACTION
END
Return 0

when i run it in SQL i get no errors, however when i call the stored procedure from .Net i get this error:
"Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.\r\nTransaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0."