1

I have a customer table and an order table in an sql server 2000 database.

I don't want an order to be in the order table with a customerID that doesn't exist in the customer table so I have put a foreign key constraint on customerID.

This all works fine but when writing a stored procedure that could possibly violate the constraint, is there a way to check whether the constraint will be violated and, if it will be, skip/rollback the query?

At the minute all that happens is the stored procedure returns an error that is displayed on my asp page and looks rather ugly + most users wont understand it.

I would like a more elegant way of handling the error if possible.

Thanks

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
chester600
  • 45
  • 1
  • 5

2 Answers2

0

You have two options:

  1. Add error handling to catch the ugly, error inspect it to see if it's a FK constraint violation and display this to the user. This is IMHO the better solution.

  2. Add code in the stored procedure like the following:

if exists (select null from customer where customerid=@customerId )  
begin  
  --The customer existed so insert order  
end  
else  
begin  
  --Do something to tell you code to display error message  
end

With the second option you will want to watch your transactional consistency. For example what happens if a customer is deleted after your check is made.

JoshBerke
  • 66,142
  • 25
  • 126
  • 164
0

You can inspect the data before attempting the operation, or you can attempt the operation and then check the errors after each statement, then ROLLBACK etc.

But you can handle it entirely within stored procedures and return appropriately to the caller according to your design.

Have a look at this article: http://www.sommarskog.se/error-handling-II.html

In SQL Server 2005, there is a possibility of using TRY/CATCH

Cade Roux
  • 88,164
  • 40
  • 182
  • 265