Er. alokpandey's Blog

How to handle SQL error in Stored Procedure

Posted in Uncategorized by Alok Kumar Pandey on September 21, 2010

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;

Advertisements

One Response

Subscribe to comments with RSS.

  1. Brett Telegan said, on September 28, 2010 at 3:36 am

    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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: