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 |
|
anetacutrotineta
Starting Member
3 Posts |
 Posted - 2002-07-24 : 09:15:36
|
| hi, i have this sql 7 database, with relations between tables.i want to write a stored procedure that deletes some rows from a table, but if the rows can't be deleted because of constraints, the procedure would not return any errors - just continue execution, without any messages. that is, for example if i run it from query analyzer the output should be either 0 rows affected, or nothing.how do i write that i want errors not to be shown?thanks, ana |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-24 : 09:25:20
|
| You can't. If an error occurs, SQL Server will notify you. It's supposed to.If have a procedure that may cause an error, and you have the ability to test for it, then you can write some code that will avoid running the error-creating code, but that's about the best you can do.We'll need more details about your exact table structures and code to give a more detailed solution. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-24 : 09:25:25
|
| The approach you have outlined is not possible. What you need to do is construct your DELETE statement so it doesn't delete rows that will violate the constraint.<O> |
|
|
|
anetacutrotineta
Starting Member
3 Posts |
Posted - 2002-07-24 : 09:41:47
|
| table 1:id1 - auto incrementdescription - somethingtable 2:id2 - auto incrementdescription - somethingid21 - related to id1 from table1i write an asp page that can delete rows from table 1. i set contraints because i have some 15 tables that depend on table 1 (tables that look like table 2) and i don't want to check each table for records related to what i delete. i want the page to act like this: if the record from table 1 is related to nothing, it can be deleted. if not, it can't be deleted, no further messages to the user. now, if it weren't asp, i could have used dblib, which works fine for this case - i get the message, and i tell the user the record can't be deleted and the application goes on. but with the asp page, if it receives the error message, the page displays it, so i am left with checking if there are any records related to the one to be deleted in 15 tables - i am looking for a way not to check them and just don't execute the statement if there are any problems and go on with the rest. maybe there is a way to turn off error messages from the scripting, not from the sql server? i use ado for connecting to sql server.thanks,ana |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-24 : 09:57:26
|
| I can certainly appreciate your frustration, but I think Rob and I where crystal clear when we said "You can't..." and "...is not possible"....A violation of a foreign key constraint raises an error....every time. There is no 'Try/Catch' type error handling in t-sql. You either need to trap the error on you presentation layer or (as I suggested) write you sql as to not generate the error.<O> |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-07-24 : 10:07:33
|
| Yes, you can turn off errors on the asp page side. If you are using VBScript, you can use the statement On Error Resume Next, and it will skip the errors and not display them. If you wanted to check and see if an error did occur, use the Err object and check the number property for a number not equal to 0.*************************Someone done told you wrong! |
|
|
|
anetacutrotineta
Starting Member
3 Posts |
Posted - 2002-07-24 : 10:55:44
|
| yep, it worked, thanks a lot.yet, rob and page were right, this fixes the problem but from asp, not from sql server.ana |
|
|
|
|
|
|
|
|
error handling question