Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Can we RAISERROR inside MERGE Statement

Answered Can we RAISERROR inside MERGE Statement

  • Thursday, March 21, 2013 12:16 AM
     
     
    Sign In to Vote
    0

    I wished to know if we can raise an error inside Merge Statement as showin in an example

    MERGE Production.UnitMeasure AS target
        USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
        ON (target.UnitMeasureCode = source.UnitMeasureCode)
        WHEN MATCHED THEN
            UPDATE SET Name = source.Name
    WHEN NOT MATCHED THEN 
      ---SET @Error = 2

      -- RAISERROR

    Basically I just wished to validate whether an error occurred in NOT MATCHED Case or not.

    Thanks

    Sindhura

     

All Replies

  • Thursday, March 21, 2013 1:01 AM
    Moderator
     
     Answered
    Sign In to Vote
    0
    You can not incorporate RAISERROR inside the MERGE statement. You can check @@ERROR right after merge to see if error happened or not or alternatively you can use TRY / CATCH and catch errors that may occur during MERGE. If there was an error, no update/inserts will be performed and MERGE statement will not change your data.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Thursday, March 21, 2013 1:38 PM
     
     
    Sign In to Vote
    0

    "an error occured in NOT MATCHED"

    It is not an error if you omit logic for the not matched case (which means you intentionally ignored those rows).  If you want to treat it as such, you can easily test for the existence of rows in the source table that do not exist in the target - either before or after the merge.  IMO, you have chosen a particularly obscure approach to updating a single (presumably) row in a table with the current value of a local variable. 

     
  • Friday, March 22, 2013 6:40 PM
     
     Answered Has Code
    Sign In to Vote
    0

    No MERGE doesn't support RAISERROR. If you want to raise error one of the way is dilute merge to INSERT UPDATE and DELETE statements

    put a try catch block and set a message

    DECLARE @msg VARCHAR(500) = ''
BEGIN TRY
     SET @msg = 'Inserting data into table'
     INSERT INTO Table
     SELECT
     SET @msg = 'Delete data'
     DELETE from table
     SET @msg = 'Update data'
     Update Table
END TRY
BEGIN CATCH
     RAISERROR('Error while performing: %s', 15, 1, @msg)
END CATCH

    • Proposed As Answer by Aalam Rangi Saturday, March 23, 2013 3:40 AM
    • Marked As Answer by Iric WenModerator Friday, March 29, 2013 7:15 AM
    •