Is it possible anyhow to raise system exception on catching exception manually?

Question

I am writing a stored procedure where i m using try catch block.

Now i have a unique column in a table. When i try to insert duplicate value it throws exception with exception no 2627.

I want this to be done like this

if (exists(select * from tblABC where col1='value')=true) raiseError(2627)--raise system error that would have thrown if i would have used insert query to insert duplicate value

EDIT:

I m using transaction which gets rollback on handling exception hence leading to increment of @@Identity value that got executed in previous queries b4 the exception occurred.

I want to check all such exception that could occur b4 actually inserting. For doing this, i would be checking for exception that could raise error manually using select statement with if else. Here i will be able to catch unique key violation but exception will not be occurred so i will be throwing exception deliberately here but that exception i want should be a system exception i.e error 2627

And which method will be better, using insert query or checking for duplicate value before insertion using Select query ?

IS IT POSSIBLE ANYHOW TO RAISE SYSTEM EXCEPTION ON CATCHING EXCEPTION MANUALLY i.e THROWING SAME EXCEPTION THAT SQL WOULD HAVE THROWN

Answer 1

You can't raise system errors. RAISERROR(2627...) is illegal. Beside the fact that you're lying (no error 2627 occurred), you're missing the inserts into the message format.

Application should never rely on IDENTITY continuity, the fact that you complain that it 'increases the autoincrement' revels your application has a bug (in design for sure, if not in code). IDENTITY values may contain gaps, is part of their specifications.

As to what is better, to insert and catch, or to try to update: it depends on your prevalent pattern. If the value is likely to exist, the UPDATE first strategy is better. If the value is likely not to exist or if the probability is unknown it is better to INSERT and catch the error, for reasons of performance and, more importantly, correctness (SELECT check is never correct under concurrency).