CODEWITHSUNDEEP
sqlsql-serverms-accesssql-server-2012
Rominus
Rominus

Reputation: 1221

Where do you put SQL RAISERROR code?

I'm trying to set up a custom error message to pass to MS Access (from SQL Server) when a user enters a duplicate key (instead of system message 2627). I've read up on sp_addmessage and RAISERROR and TRY/CATCH blocks which all make perfect sense. But nowhere I've looked does it seem to say where you put the RAISERROR code (and TRY/CATCH block) so it will actually pass back to the application. So, where does the code go?

Upvotes: 1

Views: 346

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

Don't think about it in terms of users entering duplicate keys. Instead, think in terms of users just entering keys, some of which turn out to be duplicates when you try to insert them. It's a subtle difference, but it helps you here, because it means you think in terms of having your code available for all new table inserts instead of just a specific type of insert.

When a user enters a key, an INSERT sql statement runs. If the key is a duplicate and you have the constraints defined on the table to the prevent that, the INSERT statement fails. If your Access application has you writing custom SQL, you can wrap this in a TRY/CATCH, and put the RAISERROR in the CATCH block. If your Access application is such that you never see any SQL, you may be stuck, and have to put up with the built-in behavior.

Upvotes: 4

Related Questions