Returning status/error codes and messages from stored procedures
in [Programming SQL]
|
Prev: OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Unspecified error".
Next: how to truncate to 2 decimal places
From: Andy B. on 16 Mar 2010 08:30 I have a stored procedure that inserts a row into a table. When someone tries to insert a row and violates a unique constraint, should I only return 2601 and force the client to figure out what the problem was? or should I return a 1 row resultset with the 2601 and a formatted string explaining what went wrong? Any ideas?
From: Uri Dimant on 16 Mar 2010 08:46 Hi Another option is to check within a stored procedure that value and NOT inserting a wron value , something like that IF NOT EXISTS (SELECT * FROM tbl WHERE col=(a)col)---does not exist INSERT INTO ..... "Andy B." <a_borka(a)sbcglobal.net> wrote in message news:uVBQ4RQxKHA.4240(a)TK2MSFTNGP06.phx.gbl... >I have a stored procedure that inserts a row into a table. When someone >tries to insert a row and violates a unique constraint, should I only >return 2601 and force the client to figure out what the problem was? or >should I return a 1 row resultset with the 2601 and a formatted string >explaining what went wrong? Any ideas? >
From: Plamen Ratchev on 16 Mar 2010 10:29 As Uri noted it may be best to prevent this from happening at all. If you prefer to send error, then it is really your preference on how you handle errors. I would normally just raise an error with the message and then handle this in the app layer. -- Plamen Ratchev http://www.SQLStudio.com
From: Andy B. on 16 Mar 2010 10:45
Got it. Decided to raise the error with try...catch. I will send the sql server error number along with a descriptive error message that a non programmer (user error) could understand. I.e.: select @error_number as number, @error_message as message; -- results on a failure: number message 2601 The headline could not be added because one with that title already exists. -- if the insert succeeded. number message 0 The headline was successfully saved. --end sample output. "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:VqednWd54t1HCALWnZ2dnUVZ_velnZ2d(a)speakeasy.net... > As Uri noted it may be best to prevent this from happening at all. If you > prefer to send error, then it is really your preference on how you handle > errors. I would normally just raise an error with the message and then > handle this in the app layer. > > -- > Plamen Ratchev > http://www.SQLStudio.com |