To execute another statement after update becomes successful

Question

To execute another statement after update becomes successful

bndgyawali

SSC-Addicted

Points: 413
More actions
April 6, 2010 at 6:49 am

#386647

Hello,
I want to run the update statement. And if the update statement is successful, then I want to run another set of statements.
So, does execute(update_statement) returns anything like in mysql??
I would like to get help if there are any other options as well..
Thanks in advance

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

bndgyawali (4/6/2010)
Hello,
I want to run the update statement. And if the update statement is successful, then I want to run another set of statements.
So, does execute(update_statement) returns anything like in mysql??
I would like to get help if there are any other options as well..
Thanks in advance

Can't speak for MySQL, but in SQL Server - 😛 - you would use a transaction and probably a TRY...CATCH construct. Post an example if you want more detail.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

david_wendelken SSCrazy Points: 2388 More actions · Answer 2

What do you mean by "update statement is successful".

Updates 1 row? >= 1 row? Doesn't throw an error?

bndgyawali SSC-Addicted Points: 413 More actions · Answer 3

I mean like

if

update tbl1 set col1='Y' where col2='xyz'

updates one or more rows, then i need to execute one set of statements. if this doesn't update any rows, then i need to execute other set of statements. how can this be achieved?

Paul White SSC Guru Points: 150468 More actions · Answer 4

bndgyawali (4/6/2010)
I mean like
if
update tbl1 set col1='Y' where col2='xyz'
updates one or more rows, then i need to execute one set of statements. if this doesn't update any rows, then i need to execute other set of statements. how can this be achieved?

Use the OUTPUT Clause:

DECLARE @Example

TABLE (col1 INTEGER NOT NULL);

INSERT @Example (col1) VALUES (1);

DECLARE @UpdatedRows

TABLE (col1 INTEGER NOT NULL);

BEGIN TRY

BEGIN TRANSACTION;

UPDATE @Example

SET col1 = col1 + 1

OUTPUT inserted.col1

INTO @UpdatedRows (col1)

WHERE col1 = 1;

IF EXISTS

(

SELECT *

FROM @UpdatedRows

)

BEGIN

-- Do other stuff

PRINT 'Update succeeded, doing other stuff...'

END;

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

PRINT 'Error updating. Details follow:'

PRINT ERROR_MESSAGE();

END CATCH;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 5

You can also use @@ROWCOUNT UPDATE MyTable

SET Col1 = Val1 ...

IF @@ROWCOUNT > 0

BEGIN

-- DO other updates

ENDIf you have triggers on your table thay could affect the value of @@ROWCOUNT, so just be ware of that.

Paul White SSC Guru Points: 150468 More actions · Answer 6

Lamprey13 (4/6/2010)
If you have triggers on your table thay could affect the value of @@ROWCOUNT, so just be aware of that.

There are a number of scenarios where @@ROWCOUNT may produce unexpected results (MERGE being another example). The trigger issue is why I posted the OUTPUT clause solution - you never know when someone might add a trigger...

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

bndgyawali SSC-Addicted Points: 413 More actions · Answer 7

Thanks everybody for your warm suggestions. I used output inserted.col1 for my task.

I tried to use @@rowcount but it returned 0 for update statement. So, i couldn't use this.

Thanks again to all.

Paul White SSC Guru Points: 150468 More actions · Answer 8

Thanks for the feedback.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi