CODEWITHSUNDEEP
sql-servervb.netwinformsstored-procedures
Smile Azeez
Smile Azeez

Reputation: 281

SQL Stored procedures and error handling

I use Stored procedures all the time. but i never used error handlers inside stored procedures. I just came to know about error handlers. I tried to do it in my stored procedures but i never got the error message in my application. i am giving my stored procedure below 

ALTER PROCEDURE   [dbo].[E_AddDesignation]

    @Designation nvarchar(100),
    @Order int 

AS
BEGIN
SET NOCOUNT ON;
    DECLARE @myRowCount int

    SELECT DesignationID FROM Designation WHERE (DesignationName = @Designation)
    SET @myRowCount=@@ROWCOUNT
    IF(@myRowCount ! = 0)
        BEGIN       
            RAISERROR('Already exists',5,1)
        END
    ELSE 
        BEGIN
            insert into Designation(DesignationName,DesignationOrder) values (@Designation,@Order)
            RAISERROR('Submitted Successfully',5,1)
        END 
SET NOCOUNT OFF;
END

I am trying to call the storedprocedures and to get the error message like below.

Try            
            c.con.Open()
            c.cm = New SqlCommand("E_AddDesignation", c.con)
            c.cm.CommandType = CommandType.StoredProcedure
            c.cm.Parameters.Add("@Designation", SqlDbType.NVarChar).Value = Designation
            c.cm.Parameters.Add("@Order", SqlDbType.Int).Value = Order           
            c.cm.ExecuteNonQuery()
            c.con.Close()
        Catch ex As SqlException
            Console.WriteLine(ex)
        End Try

i am done something wrong somewhere. please help me to to find out the mistake.

Upvotes: 2

Views: 3942

Answers (3)

Nilesh Thakkar
Nilesh Thakkar

Reputation: 2895

In your code you're raising error with severity 10. 

 RAISERROR('Already exists',5,1)

So SQLConnection will treat it as InfoMessage and not exception. The InfoMessage event occurs when a message with a severity of 10 or less is returned by SQL Server. Messages that have a severity between 11 and 20 raise an error and messages that have a severity over 20 causes the connection to close. Please check here

You might want to check database error severities here

You can wire up infomessage event as shown below:

// Assumes that connection represents a SqlConnection object.
   connection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);

protected static void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)
{
  foreach (SqlError err in args.Errors)
  {
   Console.WriteLine("The {0} has received a severity {1}, state {2} error number {3}\n" +
   "on line {4} of procedure {5} on server {6}:\n{7}",
   err.Source, err.Class, err.State, err.Number, err.LineNumber, 
   err.Procedure, err.Server, err.Message);
  }
}

For more information check this

To handle exception in your stored procedure itself, you can use Try..Catch block from SQL Server 2005 onwards.

Edit: You may want to check this that shows

HOW TO: Return Errors and Warnings from a SQL Server Stored Procedure in ADO.NET

I hope it helps!

Upvotes: 2

praveen
praveen

Reputation: 12271

You have specified a severity level of 5 (RAISERROR('Already exists',5,1)) which according to MSDN means

Informational messages that return status information or report errors
that are not severe. The Database Engine does not raise system errors with
severities of 0 through 9.

You should return 16 which is the default severity level

Upvotes: 2

XN16
XN16

Reputation: 5869

I could be wrong, but I believe it will be down to your use of SET NOCOUNT ON;.

Try moving the SET NOCOUNT OFF; line to the line immediately below your SET @myRowCount=@@ROWCOUNT line. Then the stored procedure should be able to return a value.

I seem to remember having a similar issue a few years ago.

Also as a side note you could replace the following chunk of code:

SELECT DesignationID FROM Designation WHERE (DesignationName = @Designation)
SET @myRowCount=@@ROWCOUNT
IF(@myRowCount ! = 0)

with a smaller (and in my opinion nicer) piece of code like this:

IF NOT EXISTS(SELECT * FROM Designation WHERE DesignationName = @Designation)

I'm just mentioning this as I used to use the method your using for years until I found the EXISTS keyword!

Upvotes: 1

Related Questions