Helps requires in SQL-which has update, insert statement alternatively

I am writing a sql script, which has update, insert statement alternatively. update statement is written on the basis of some conditions. if update statement executes successfully then next insert statement will execute.

 

If SQL does not update any records next insert statement should not be executed .

 

Is there a way to identify  that update was done successfully? so this can be used to determine insert statement execution?

 

PLease reply

 

SOLUTION 1:

 

Microsoft SQL Server return the @@ROWCOUNT variable to return the number of rows affected by the last statement

 

SOLUTION 2:

 

Use the statement

 

Select @@Rowcount

 

After the update statement, if the result is 1 it means the row has been updated successfully.

If the output is 0, then the update statement has been failed.

 

SOLUTION 3:

 

You can use ‘@@Rowcount’ to achieve your current requirement.

The following example executes an UPDATE statement and uses @@ROWCOUNT to detect if any rows were changed.  If no rows were changed (WHERE condition not met, therefore SET not performed), then an INSERT is performed equivalent to the attempted UPDATE.

Example:

 

                    UPDATE Employee

SET <field> = <value>
...
WHERE <field> = <key>

IF @@ROWCOUNT = 1

BEGIN
    INSERT Employee (<fields) VALUES (<values>)
END

                    ELSE

                                (Your Customized Message Here…………….) ( Ex: @@MESSAGE ‘SQL failed to update records hence new record should not be inserted’ )

 

 

 

Note:  

 

After the update statement, if the result is 1 it means the row has been updated successfully. If the output is 0, then the update statement has been failed. Make sure that you should have to implement Transactions and RollBack features in your DB Script for accurate results.