Thursday, March 21, 2013 12:16 AM
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
Basically I just wished to validate whether an error occurred in NOT MATCHED Case or not.
Thursday, March 21, 2013 1:01 AMModeratorYou 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
Thursday, March 21, 2013 1:38 PM
"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
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