Click here to Skip to main content
14,866,384 members
Please Sign up or sign in to vote.
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
Updated 7-Oct-20 5:14am
v4
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

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.
   
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."

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900