Solved

Proc error handling.  vb.net

Posted on 2014-12-11
2
216 Views
Last Modified: 2014-12-12
The following proc deletes records in tblSoftware. SoftwareID from this table is used as FK in another table. On delete, it will produce some errors ("There are some records with this SoftwareID in tblOrderDetails; cannot delete this item.").

What are options to show this error message.

Question: How to catch this error in try/catch with multiple catch for this error and some other error may happen?

ALTER PROCEDURE [dbo].[spSoftwareDelete]
     @SoftwareID int
As
BEGIN

	Delete From tblSoftware Where SoftwareID = @SoftwareID 
   
	if @@Error>0 
        Begin
		 -- Set @msg = @msg + '; SQL Server error: ' + CAST(@@Error AS NVARCHAR(10))
		-- Raise an error and return
		RAISERROR ('There are some records with this SoftwareID in tblOrderDetails; cannot delete this item..', 16, 1)
		RETURN @@ERROR
        End 
END 

Open in new window

0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Accepted Solution

by:
LordWabbit earned 500 total points
ID: 40495549
Well it would be best to check for the FK restraint first, rather then just trying to delete and failing.  That way you can decide what message needs to be sent, as well as having a catch all for unexpected errors (deadlock's etc.)
Something like
ALTER PROCEDURE [dbo].[spSoftwareDelete]
     @SoftwareID int
As
BEGIN
	DECLARE @Check INT
	SET @Check = (SELECT COUNT(*) FROM OtherTable WHERE SoftwareID = @SoftwareID)
	IF (@Check > 0)
	BEGIN
		RAISERROR ('There are some records with this SoftwareID in tblOrderDetails; cannot delete this item..', 16, 1)
		RETURN @@ERROR
	END
	ELSE
	BEGIN
	Delete From tblSoftware Where SoftwareID = @SoftwareID 
   
	if @@Error>0 
        Begin
		 -- Set @msg = @msg + '; SQL Server error: ' + CAST(@@Error AS NVARCHAR(10))
		-- Raise an error and return
		RAISERROR ('There are some records with this SoftwareID in tblOrderDetails; cannot delete this item..', 16, 1)
		RETURN @@ERROR
        End 
END 

Open in new window

0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40495943
Thank you.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question