This is an ideal place for BEGIN TRAN / COMMIT TRAN / ROLLBACK TRAN. I've
done this in the past with SQL Server 7.0 and it works great.
DECLARE @ErrNum int
SET @ErrNum = 0
-- delete the line item
BEGIN TRAN
delete Invoice_Line_Item
Where Invoice_Line_Item_ID = @Line_Item_ID
set @ErrNum = @ErrNum + @@error
-- lots more SQL code can go here if you want
if @ErrNum <> 0
begin
-- an error has occured while deleting
ROLLBACK TRAN
print @ErrNum
return @ErrNum
end
else
begin
COMMIT TRAN
end
|------------------------------------+------------------------------------|
|Eric A. Laney |"Time wounds all heels." |
|Systems Engineer | |
|LAN Optimization Team | |
|Voice: 813.978.4404 | - Jane Ace|
|Pager: 888.985.8519 | |
|------------------------------------+------------------------------------|
"Shawn Grover"
<sgrover@OBJECT To: CF-Talk <[EMAIL PROTECTED]>
WORKS.com> cc:
Subject: Exception Handling in SQL?
07/31/2001
04:07 PM
Please respond
to cf-talk
We are using SQL Server 7, and need to delete records. However, we need to
intercept if a foriegn key constraint error is thrown. SQL Server seems to
be throwing an exception as soon as the error is encountered, and not
giving
our script a chance to handle the error.
Is there anyway around this? Other than checking the foriegn key tables
first that is...
here's what we are trying thus far:
-- delete the line item
delete Invoice_Line_Item
Where Invoice_Line_Item_ID = @Line_Item_ID
set @Error = @@error
if @Error <> 0
begin
-- an error has occured while deleting
print @Error
return @Error
end
- An exception is being thrown before the Print statement, we need to avoid
the exception.
Thanks.
Shawn Grover
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
