Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maurkb
Starting Member
5 Posts |
 Posted - 2007-09-18 : 15:03:40
|
| 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 - 2007-09-18 : 15:09:25
|
| [code]IF NOT EXISTS(SELECT * FROM TABLE WHERE KEY = @KEY) INSERT INTO TABLE(ColList) SELECT @VariableList[/code]???????Not sure what ou driving at though about the try again thingExamples provide the best method of commeunicationRead the hint link in my sigBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-18 : 15:10:26
|
| http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
maurkb
Starting Member
5 Posts |
Posted - 2007-09-18 : 15:15:11
|
| 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. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-09-18 : 15:36:56
|
What makes you think that future attempts will ever allow the insert?DECALRE @x intSET @x = 1WHILE @x < 10000000000000 BEGIN IF NOT EXISTS(SELECT * FROM TABLE WHERE KEY = @KEY) INSERT INTO TABLE(ColList) SELECT @VariableList SET @X = @x + 1 END Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
maurkb
Starting Member
5 Posts |
Posted - 2007-09-18 : 16:38:39
|
| Ahh. Thanks for all the info. It was certainly helpful. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-09-18 : 17:49:43
|
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 tablehttp://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspxBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
|
|
|
Error handling