SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Error handling
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maurkb
Starting Member

5 Posts

Posted - 09/18/2007 :  15:03:40  Show Profile  Reply with Quote
I have a stored procedure that inserts a new record into a table. The table it inserts into has a unique constraint that utilizes an identity column. Occassionally, this constraint gets violated due to the fact that the app running my stored procedure isn't the only application inserting into the table. Ideally, we would want to change the design to avoid this scenario but these are legacy applications that will eventually be sunsetted. My question is this; is there a way to handle the unique key constraint violation and prevent an exception being thrown in the app calling the stored proc? I know I can check for the error code and handle it within the stored proc but my app still gets an exception caught by the calling method. Can I prevent this from happening? I want for the insert to attempt again and if successful continue processing and not produce the exception. Any help would be greatly appreciated.

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  15:09:25  Show Profile  Reply with Quote

IF NOT EXISTS(SELECT * FROM TABLE WHERE KEY = @KEY)
  INSERT INTO TABLE(ColList) SELECT @VariableList


???????

Not sure what ou driving at though about the try again thing

Examples provide the best method of commeunication

Read the hint link in my sig



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam




Edited by - X002548 on 09/18/2007 15:09:45
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 09/18/2007 :  15:10:26  Show Profile  Visit spirit1's Homepage  Reply with Quote
http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

maurkb
Starting Member

5 Posts

Posted - 09/18/2007 :  15:15:11  Show Profile  Reply with Quote
By trying again I mean I can catch the error (violation of unique constraint) and if it is indeed that error I will attempt to insert again since the first insertion attempted to insert with a value that already existed (the column i'm referring to increments 1 for each record - it is not an identity column, sorry to confuse). I have already proven that I can do this, the issue is that SQLServer still causes my calling method to catch an exception and processing stops. I want to prevent the exception from being thrown.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  15:36:56  Show Profile  Reply with Quote
What makes you think that future attempts will ever allow the insert?


DECALRE @x int
SET @x = 1
WHILE @x < 10000000000000
  BEGIN
    IF NOT EXISTS(SELECT * FROM TABLE WHERE KEY = @KEY)
      INSERT INTO TABLE(ColList) SELECT @VariableList
    SET @X = @x + 1
  END



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

maurkb
Starting Member

5 Posts

Posted - 09/18/2007 :  16:38:39  Show Profile  Reply with Quote
Ahh. Thanks for all the info. It was certainly helpful.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  17:49:43  Show Profile  Reply with Quote
quote:
Originally posted by maurkb

By trying again I mean I can catch the error (violation of unique constraint) and if it is indeed that error I will attempt to insert again since the first insertion attempted to insert with a value that already existed (the column i'm referring to increments 1 for each record - it is not an identity column, sorry to confuse). I have already proven that I can do this, the issue is that SQLServer still causes my calling method to catch an exception and processing stops. I want to prevent the exception from being thrown.



Ahhhhh...you need a next id table

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000