If Statement

[patelaashish]

Hi,

I am using the following if statment in my sql code. The problem i have is that the update statment runs successfully irregardless of wether my insert statment is successfull or not. What I need to do is if the insert is successfull run the update statement and if the insert fails at any point the update statment should fail.

The SQL statment is as follows:

IF EXISTS (select from table1statment stating the condition to excute the insert statment)
INSERT INTO table1(a,b)
select (a,b) from table2
UPDATE table1
SET flag = 'DONE' WHERE condition.


Thanks.

[srinit]

Hi,

I am using the following if statment in my sql code. The problem i have is that the update statment runs successfully irregardless of wether my insert statment is successfull or not. What I need to do is if the insert is successfull run the update statement and if the insert fails at any point the update statment should fail.

The SQL statment is as follows:

IF EXISTS (select from table1statment stating the condition to excute the insert statment)
INSERT INTO table1(a,b)
select (a,b) from table2
UPDATE table1
SET flag = 'DONE' WHERE condition.


Thanks.

Hi
Use transactions
by putting the insert and update statements in a transaction you can get this.

[patelaashish]

Hi
Use transactions
by putting the insert and update statements in a transaction you can get this.

Thanks for the help

How can I get the transaction to fail so that I know it works by putting it into transaction.

Thanks

[SkinHead]

It Might Be Just As Easy To Check The @@Error And @@ROWCOUNT System Variables After The INSERT Statement.

Eg

If EXISTS (....)
BEGIN
INSERT...

Check If No Error & Row Successfully Inserted.
IF (@@Error == 0) AND (@@ROWCOUNT <> 0)
BEGIN
UPDATE....
END
END

[ck9663]

Hi,

I am using the following if statment in my sql code. The problem i have is that the update statment runs successfully irregardless of wether my insert statment is successfull or not. What I need to do is if the insert is successfull run the update statement and if the insert fails at any point the update statment should fail.

The SQL statment is as follows:

IF EXISTS (select from table1statment stating the condition to excute the insert statment)
INSERT INTO table1(a,b)
select (a,b) from table2
UPDATE table1
SET flag = 'DONE' WHERE condition.


Thanks.

try:
IF EXISTS (select...)
BEGIN
begin transaction
INSERT INTO table1(a,b)
select (a,b) from table2
UPDATE table1
SET flag = 'DONE' WHERE condition.

---Check If No Error & Row Successfully Inserted.
IF (@@Error == 0) AND
commit
else
BEGIN
rollback transaction
--put other error handling scripts here
END

END

consider this:
1. @@error checks if the previous statement runs successfully. in this case the last statement is UPDATE, not INSERT. you can check the @@error after the insert and rollback or commit depending the result. then do the UPDATE, then check it again.

[SkinHead]

There Seems To Be A Number Of Issues Here.

There Are 5 Steps

If EXISTS
BEGIN
INSERT
SELECT
UPDATE
SET
END

From What You Said, You Only Want To Continue To The Next Step If The Previous Step Was Successful.

That's Fine, But You May Need To Consider Why It Might Fail & Do Something About It.

Also, If Say The UPDATE Fails, Do You Want To RollBack The INSERT ???

If So, Then You Will Need To Use A Transaction, As My Learned Colleague Suggested. You'll Need To Check For Success/Failure After Each Of The INSERT/SELECT/UPDATE Statements And Use A GOTO Statement To Jump To The End Of The Procedure If There Is An Error.

[patelaashish]

try:
IF EXISTS (select...)
BEGIN
begin transaction
INSERT INTO table1(a,b)
select (a,b) from table2
UPDATE table1
SET flag = 'DONE' WHERE condition.

---Check If No Error & Row Successfully Inserted.
IF (@@Error == 0) AND
commit
else
BEGIN
rollback transaction
--put other error handling scripts here
END

END

consider this:
1. @@error checks if the previous statement runs successfully. in this case the last statement is UPDATE, not INSERT. you can check the @@error after the insert and rollback or commit depending the result. then do the UPDATE, then check it again.

Thanks a lot mate. I will try this later and let you know the results. sounds ok.