Best Practice Error Checking

              As a rule of thumb, is it better to let the DB engine throw an 
error back to the caller, or do you do it in your SP code? In the case of a 
user already existing in a table,  should I check that at the start of my 
SP, or let the Engine catch it as a PK violation and send the message to to 
client?
Always looking to improve my code... I bet you this is one of those ... It 
depends .......
Thanks
Paul

0
Paul
4/24/2010 1:35:03 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
610 Views

Similar Articles

[PageSpeed] 6

> As a rule of thumb, is it better to let the DB engine throw an error back 
> to the caller, or do you do it in your SP code? In the case of a user 
> already existing in a table,  should I check that at the start of my SP, 
> or let the Engine catch it as a PK violation and send the message to to 
> client?

It's generally best to check for anticipated error conditions rather than 
relying on a constraint violation for errors that you anticipate.  The below 
RAISERROR example also returns a non-zero return code to the caller.

> Always looking to improve my code... I bet you this is one of those ... It 
> depends .......

You are right that "it depends", though.  If the application has previously 
verified that the user doesn't exist, a duplicate insert attempt will be 
rare (i.e. race condition) so you can simplify your code and let SQL Server 
throw the error in the event of a constraint violation or other unexpected 
error.  Be aware that if you use TRY/CATCH, you must use RAISERROR to throw 
the error back to the caller.

CREATE PROC dbo.usp_InsertUser
        @UserID int,
    @UserName varchar(30)
AS,
IF EXISTS(SELECT * FROM dbo.Users WHERE UserID = @UserID)
BEGIN
    RAISERROR('Specified UserID already exists', 16, 1);
    RETURN 1;
END

INSERT INTO dbo.Users(UserID, UserName)
VALUES(@UserID, @UserName);

RETURN @@ERROR;
GO

-- 
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Paul Ilacqua" <pilacqu2@twcny.rr.com> wrote in message 
news:10E983DD-3C73-42A0-ADF3-29F86945104E@microsoft.com...
>              As a rule of thumb, is it better to let the DB engine throw 
> an error back to the caller, or do you do it in your SP code? In the case 
> of a user already existing in a table,  should I check that at the start 
> of my SP, or let the Engine catch it as a PK violation and send the 
> message to to client?
> Always looking to improve my code... I bet you this is one of those ... It 
> depends .......
> Thanks
> Paul
> 
0
Dan
4/24/2010 2:17:39 PM
Thanks Dan for the speedy reply...

"Paul Ilacqua" <pilacqu2@twcny.rr.com> wrote in message 
news:10E983DD-3C73-42A0-ADF3-29F86945104E@microsoft.com...
>              As a rule of thumb, is it better to let the DB engine throw 
> an error back to the caller, or do you do it in your SP code? In the case 
> of a user already existing in a table,  should I check that at the start 
> of my SP, or let the Engine catch it as a PK violation and send the 
> message to to client?
> Always looking to improve my code... I bet you this is one of those ... It 
> depends .......
> Thanks
> Paul
> 

0
Paul
4/24/2010 3:59:39 PM
Reply:

Similar Artilces:

The best photographer
He is the best famous photographer.. http://club.cyworld.com/dcnomad ...

Outlook 2k is getting Runtime error
How doing you fix the above? ...

Transfer to PO error
An interesting Error poped up when attempting to transfer a requisition to PO on our Business Portal 3.0 site. It states... Query error. Microsoft.BusinessFramework.Data.GeneralDatabaseException Line 1:Incorrect syntax near 'T0'. I have checked the event log and have found nothing out of the ordinary. Any ideas would help. Nathan, try the following: To resolve this problem, run the iisreset command to reset Internet Information Services (IIS). To do this, follow these steps: 1. Make sure that you have a complete backup copy of the database. You can use this backup copy to res...

OWA HTTP 404 Error
I have exchange 2000 server Enterprise Edition. I have several storage group. In one storage group, I create several users. But those users have problem using OWA access. They have HTTP enabled, I tried different aliases and so on, but those users always HTTP 404 - File not found, Internet Information Services. Other users in different storage group, does not have these kind of problem. Hi, Do the users with OWA errors have also an SMTP E-mail address with the same domain as the users where OWA works? (SMTP address does not have to be their default address but must exist for SMTP dom...

Sending & Receiving Errors
Every time I go to send or receive button on Microsoft Outlook a error comes up at the bottom right hand corner I press on the error and a box comes up Outlook Send & Receive Progress box comes up, with tasks and errors. Karla, you wrote on Tue, 17 Jul 2007 03:52:04 -0700: > Every time I go to send or receive button on Microsoft Outlook a error comes > up at the bottom right hand corner I press on the error and a box comes up > Outlook Send & Receive Progress box comes up, with tasks and errors. And the error messages you receive are secrets? -- Best Regards Christi...

Best text books / manuals for MS Office (intermediate-Advanced use
What are the best (most highly reccomended) text books / instruction manuals you can get for Access, Excel, PowerPoint, Publisher, & Word and also VBE? I consider myself to be Quite proficient in Excel......Can produce a decent document in Publisher.......Can "Get by" in Word....Not enough knowledge of Access......Only used PowerPoint once....... I started using BASIC (the programming language) back in the '80s, so I have some knowledge of VBE (Formulas & syntax are quite simular from what I see now). I have created several Excel projects with Macros bu...

MAPI error, BES, and Exch 2007
I'm trying to get blackberry enterprise server working with Exchange 2007. Yes, I know its not supported... This wasn't my bright idea. After spending entirely too much time on the phone with blackberry support, they say my problem is a version conflict of mapi32.dll's on the exch 2007 box and the BES box. The problem is, I'm unable to register the updated mapi version from the BES box on the E2007 box. I'm guessing this has something to do with the 64-bit system, but am not sure. Also, there is a mapi32.dll on the E2007 box in a C:\windows\SysWOW64 folder in addi...

Requisitions Management (Business Portal) error
Hi all, I'm desperately trying to find a resolution for this problem... I'm trying to approve a requisition in Requisitions Management, but I keep getting the error message "Unable to reconcile changes for entity". Re-vreating the requisition doesn't work, and the supplier seems to be set up the same way as every other one. We have no problems with other requisition requests... Any ideas? I'm at a loss and there's nothing about this error on Knowledge Base. Thanks Shaun Shaun, Does the requisition have any comments on it? There was an issue with a carriage...

An Error has occurred loading MS CRM functionality
New load of the CRM client software and on Outlook start I get: "An error has occurred loading MS CRM functionality. Try restarting Outlook" Not very informative and of course restarting provides the same error. Anyway to get more info on what is crapping out about it? John, I had some similar issues with some clients during my 3.0 upgrade. Are you on 1.2 or 3.0? Also, are there any errors in your Event logs on that machine? Some other things to try: Remove any cached passwords pointing to the CRM server: ... Navigate to the Control Panel 2. Select 'User Accounts' ...

Where is the best place to put notes?
We are new to MSCRM and are not used to all of the possible places that you can enter notes. Has anyone created a structure for their company as to what notes should be entered where? Wendy, There are many ways to handle notes. You have accurately identified that notes are kind of disjointed. What you might consider doing is creating a central notes depository in Sharepoint and then adding a customization with an inline frame that shows the Sharepoint notes repository in several different parts of CRM. In general, you have not provided enough information on what you are trying to ac...

Setting up Exchange 2003
I had this problem the other day while extending the schema in preparation for an upgrade to Exchange 2003. I searched through a lot of databases and search engines, and found that a lot of people have had this problem, but none of the forums led to a clear cut answer to the issue. So, I thought I'd share my solution to try and help others out, and save them some time and frustration. I was ready to call Microsoft support right before I figured this out. While running forest prep (setup /forestprep), setup failed with Error code 0XC103798A. It then specified to check the Exchange inst...

Error 0x80070057 in Outlook 2003
Sending email to a specific recipient causes 0x80070057 error. Outlook 2003 on WinXPPro SP2. The strange thing is that it occurs only with THAT contact. What does it means? thx. m marcello.tamburini@gmail.com <marcello.tamburini@gmail.com> wrote: > Sending email to a specific recipient causes 0x80070057 error. Outlook > 2003 on WinXPPro SP2. The strange thing is that it occurs only with > THAT contact. > > What does it means? http://www.google.com/search?q=0x80070057 I've scanned some of the references and found a mention of a product called "Bells and Whi...

auto number conversion error
I am trying to convert a database from 97 to 2000 and am getting some ambiguous auto number results. The last auto number from prior to the conversion is 120 and now when I enter a new record after conversion the numbers start at 1291848529 and continue from here. I have a backup to try and convert again, but the same thing keeps happening. Is there something I can do to fix this so the next # will be 121 either in the converted database or somehow through the coversion process? Please, any help? These new #'s are way too large. Thanks, Bridget ...

who to blame for this kind of retruned error message?
Hi, My situation is like this. Our organization is able to send emails to this company, however, this company can't send us email nor replying from the email that we sent. The guy who works for this company fax me the error message that return to his mailbox. The email was sent from Jerry (our organization- OMG Company) to Mike (another company- XYZ Company). Below was the message received by Mike when he tried to respond to our email. From: System Administrator To: Jerry McGuire Sent: Monday, Jan 5 Subject: Problem Your message did not reach some or all of the intended recipients. ...

what is the best way to resize test that "best fits" the text box
I just want to know how to resize text to fit the text box. Select the text and reduce the font size until it suits your requirements. There is no automatic function for this. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "utahjohn" <utahjohn@discussions.microsof...

Error saving a Shared Workbook
I have a user using a shared workbook and gets an error everytime she does this ONE specific change. She deletes a line and then saves and gets a message that says, "EXCEL.exe has generated errors and will be shutdown by Windows... blah blah blah". If she makes anyother type of changes it saves just fine. I also had another user do the SAME change and that person also got that error message. When the file is changed to "Not Shared" then the line is deleted it saves fine also. The problem is obviously with the file itself and that is shared out. can anybody ...

Best way to forecast individual sales territories?
I have a spreadsheet with twelve months of sales data per territory (100 territories.) I need to forecast the next three months. The product being sold is just moving from launch phase to high growth phase, so it's still growing pretty quickly. What is the best method to forecast each individual territories' sales over the next three months? ...

OWA/OMA Protection
<No response from other forum> Hello, I have a Exchange 2003/OWA frontend that is protected with RSA two factor authentication inside a Checkpoint Firewall. The RSA part works fine and everything is good. Now I buy a Motorola Droid and need to set up the Exchange Sync and the RSA poses a problem. If I remove the RSA protection the Droid connects to the OWA and syncs well. Calls to RSA says it will not work with a Droid so I'd have to get rid of the RSA authentication. The question is: it looks like if I want to use a Android 2.0 based Droid phone to sync with outlook...

Strange Event Log Error
All - I have a new install of Ex2003 on a Win2003 SP1 server. One Forest and one domain. The domain consists of two sites and each site has at leat one DC/GC in it. Replication in this topology is working great and no Win2003 domain errors are deing reported. My exchange looks the same. In the ExAdmin, I have one administratove group and then two Exchange 2003 servers in that group. Everything seems to work fine. Mail goes both direct and each server is responsible for outbout email. Here's teh event that I get in my log on restarts and ocosionally thoughout a week.: Event Type: Warni...

Error in WdfRequestRetrieveOutputBuffer and WDF_MEMORY_DESCRIPTOR_INIT_BUFFER
The following code snippet: VOID UsbDfu_EvtIoDeviceControl( IN WDFQUEUE Queue, IN WDFREQUEST Request, IN size_t OutputBufferLength, IN size_t InputBufferLength, IN ULONG IoControlCode ) { WDFDEVICE device; device = WdfIoQueueGetDevice(Queue); pDevContext = GetDeviceContext(device); switch(IoControlCode) { case DFU_GETSTATE : getstate(device ,Request); break; default : ... } } NTSTATUS getstate(WDFDEVICE Device,WDFREQUEST hRequest) { PURB pUrb = NULL; WDFMEMORY ...

GL figures
what really is the best practice method for brining in monthly GL figures so users can get comparative statements this year/lastyr. do you bring in all the old detail journals? or do you load via a maintenance or IM the historical balance for each month in some file. -- Linda W. Linda, You mean when you are starting up a new company? If so, unless someone needs the detail imported into GP for other reasons, I bring in a beginning balance and monthly net changes only. For example, let's say you're starting to use GP with January 2009, your fiscal year ends in December and y...

Coding error
I have a code (see below) that keeps receiving a run-time error (2166) that states "you can't lock a control while it has unsaved changes"...I have in my code a command that should save the record before it actually locks the field but for some reason does not seem to work. Can anyone see what might be wrong with my code that would be causing this to happen? Here is my code: Private Sub NoResponse_Checkbox_Click() If Me!NoResponse_Checkbox = True Then Me!Update_Letter_Comments.Locked = False DoCmd.GoToControl "Update_Letter_comments" ...

Help Please Activex error
running RMS 2.0. when i try to configure the system manager-accounting to quickbooks 2003 or later i get Eror: ActiveX component can't create object. I am stuck i have registerd many dll files as advised but no luck. any suggestions would be greatly apprectiated -- Thanks Ian ...

i get the error xml file invalid why?
when i click on the outlook button it tries to load then it comes up Cannot start Microsoft Office OUtlook. Cannot open the Outlook window. Invalid XML, the view cannot be loaded. then click ok I do not understand this because nothing to change anything. Charlene wrote: > when i click on the outlook button it tries to load then it comes up > Cannot start Microsoft Office OUtlook. Cannot open the Outlook window. > Invalid XML, the view cannot be loaded. then click ok I do not understand > this because nothing to change anything. Exit Outlook. Then run: outl...

Error 1325
Everytime I want to acceess any office software (word, excel, publisher etc), I oftn get this error message (Error 1325: Publisher is not a valid short name.) Could you help me out? Thank you. My unit is an ASUS Eee PC. I'm accessing Office through external hard drive. And did you install Office on this PC whilst the external was connected? And does the external retain the origonal drive letter that was given when you installed it? "dymygebe" <dymygebe@discussions.microsoft.com> wrote in message news:0652CBE8-9124-468C-9C9F-044E6B0FEC82@microsoft.com... ...