How to handle SQL error in Stored Procedure
When writing SQL procedures, it is really important to handle errors cautiously. Having that in mind will probably save your efforts, time and money. I have been working with MS-SQL 2000, MS-SQL 2005, MS-SQL 2008 (I have not got the opportunity to work with MS-SQL 2008 R2 yet) for many years now and I want to share with you how I handle errors in T-SQL Stored Procedure. This code has been working for many years now without a hitch.
BEGIN TRANSACTION;
BEGIN TRY
— Code in transaction go here COMMIT TRANSACTION;
Declare @myVal int
Set @myVal = ‘dfgdfd’
Select @myVal
END TRY
BEGIN CATCH
— Rollback on error
ROLLBACK TRANSACTION;
— Raise the error with the appropriate message and error severity
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int;
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY();
Select @ErrMsg as ErrMessage,@ErrSeverity as ErrSeverity
–RAISERROR(@ErrMsg, @ErrSeverity, 1);
END CATCH;
Er. alokpandey's Blog 
Hi,
In conjunction with VB.Net, it can be useful to communicate success back to the application as well as dumping a neat debugging diagnostic error like this (sorry for the verbosity):
CREATE PROCEDURE [dbo].[usp_accounts_login_add]
(
/* @ID as [int] , */ /* IDENTITY Field */
@login_name nvarchar(55), /* Not NULL */
@login_password nvarchar(55), /* Not NULL */
@email nvarchar(55) =”,
@last_session_date nvarchar(55) =”,
@last_session_id nvarchar(55) =”
)
AS
BEGIN
SET NOCOUNT OFF;
INSERT INTO Accounts
(login_name, login_password, email, last_session_date, last_session_id)
VALUES (LTRIM(RTRIM(@login_name)),
LTRIM(RTRIM(@login_password)),
LTRIM(RTRIM(@email)),
LTRIM(RTRIM(@last_session_date)),
LTRIM(RTRIM(@last_session_id)))
SELECT ID_ADDED = SCOPE_IDENTITY() /* Return the ID of newly updated record */
/* SCOPE_IDENTITY() has no meaning here */
/* and this is included to verify we got */
/* to the end without bombing out and make */
/* sure we have the right SP in VB */
END
GO
And then, in the code behind interact with the user or caller procedure as in:
Private Function CheckNewLogin(ByVal UserName As String, _
ByVal Password As String, _
ByVal EmailAddress As String, _
ByVal SessionID As String) As Integer
‘============================================
‘ Simpler with typed Dataset
‘Dim loginAdapter As New usp_account_studentsTableAdapters.Account_StudentsTableAdapter
‘Dim logintable As usp_account_students.Account_StudentsDataTable
‘Dim loginrow As usp_account_students.Account_StudentsRow
‘logintable = loginAdapter.GetLoginDataBy(UserName, Password)
‘For Each loginrow In logintable
‘ Debug.WriteLine(“loginrow: ” & loginrow.login_name & “: ” & _
‘ loginrow.login_password & “”)
‘Next
‘============================================
‘ Even simpler with typed Dataset and a custom stored procedure
‘ Allows for negative return error reporting and multiple accounts
Dim loginAdapter As New _
usp_accountsTableAdapters.AccountsTableAdapter
Dim intCheckedAccountID As Integer = 0
Dim intNewAccountID As Integer = 0
txtLoginResult.Text = “”
btnLogin.Visible = False
Try
intCheckedAccountID = loginAdapter.Accounts_Login_Check(UserName, Password)
If (intCheckedAccountID > 0) Then
txtLoginResult.Text = “Previous Login Found”
Return True
End If
intCheckedAccountID = loginAdapter.Accounts_Username_Check(UserName)
If (intCheckedAccountID > 0) Then
txtLoginResult.Text = “Username Already In Use”
Return False
End If
Catch ex As Exception
txtLoginResult.Text = “Login Check Error”
Return False
End Try
Try
‘==============================================================
‘
‘ Register by adding NEW account HERE
‘
‘==============================================================
intNewAccountID = loginAdapter.Accounts_Login_Add(UserName, Password, _
EmailAddress, Now().ToString, SessionID)
‘ EmailAddress, “”, SessionID) ‘ Now().ToString)
If (intNewAccountID < 1) Then
txtLoginResult.Text = "New Login Failed"
Return False
End If
Catch ex As Exception
txtLoginResult.Text = "Login Error"
Return False
End Try
txtLoginResult.Text = "New Login Added"
Return intNewAccountID
End Function
Combined with the transaction mechanism, which is classic and should have been included in this stored procedure, one begins to be able to build up a robust and comprehensive error handling methodology.
Also, Google "asp.net strongly typed dataset" for mechanisms to catch SQL data type errors by field with Intellesense at design time instead of run time. Avoidance is always better than response.
Hope it helps,
Brett