Databases
StackOverflow2013
Postsdbo
POT-SQL Reporting Duplicate Key Exception to user in simple manner
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL Reporting Duplicate Key Exception to user in simple manner
    text
    copied!<p>I have a simple country table using a identity column for the primary key. There are also columns used to contain the 2 letter and 3 letter ISO-3166 Country Codes. Each of these columns is defined as a unique index.</p> <p>On Inserts/Updates I want to simply notify the user if the ISO Code entered is already in use. I am relying on the database exception to trigger the process. I don't want to overwhelm the users with the technical details. I just want to tell him the the ISO value entered cannot be used.</p> <p>Here is the T-SQL in the Update Stored Proc I've written. It seems long and prone to future bugs and/or continual maintenance as the app grows and things change. Is there a better or simpler way? I might be a bit wet behind the ears with .net, but I thought it would be easier.</p> <pre><code>BEGIN TRY UPDATE [Country] SET [CountryName] = @CountryName, [CountryISO] = @CountryISO, [CountryISO3] = @CountryISO3, [UpdateDate] = @updateDate WHERE (([CountryID] = @CountryID) AND ([RowVersion] = @Original_RowVersion)); END TRY BEGIN CATCH DECLARE @ErrSeverity int, @ErrNumber int, @ErrLine int DECLARE @ErrMsg nvarchar(4000) SELECT @ErrSeverity = ERROR_SEVERITY(), @ErrNumber = ERROR_NUMBER(),@ErrState = ERROR_STATE(), @ErrMsg = CASE WHEN ERROR_NUMBER() = 2601 THEN CASE WHEN ISNULL(CHARINDEX('IX_COUNTRYISO3', ERROR_MESSAGE()), 0) &gt; 0 THEN 'The 3 letter ISO-3166 value entered is already in use. Please enter a unique 3 letter ISO-3166 value.' WHEN ISNULL(CHARINDEX('IX_COUNTRYISO', ERROR_MESSAGE()), 0) &gt; 0 THEN 'The 2 letter ISO-3166 value entered is already in use. Please enter a unique 2 letter ISO-3166 value.' ELSE ERROR_MESSAGE() + '(SQL ErrNo: ' + CONVERT(varchar(50), ERROR_NUMBER()) + ')' END ELSE ERROR_MESSAGE() + '(SQL ErrNo: ' + CONVERT(varchar(50), ERROR_NUMBER()) + ')' END; RAISERROR(@ErrMsg, @ErrSeverity, @ErrState) END CATCH </code></pre> <p>Is my solution viable?<br> What is the best way to share this exception code between the Insert and Update StoredProcs?</p> <p>I guess I am essentially asking for a code review.</p> <p>Thank you very much Mike</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload