stored procedure error handling

Question

i have this procedure for inserting rows in tables(sql server 2005)

CREATE PROCEDURE ans_insert
    (
    @q_desc varchar(2000),
    @sub_id int,
    @marks int,
    @ans1 varchar(1000),
    @ans varchar(1000),
    @userid varchar(15),
    @cr_date datetime

    )
    AS
    BEGIN
    BEGIN TRY
    BEGIN TRANSACTION
        DECLARE @q_id int

        insert into questions(q_desc,sub_id,marks,created_by,DT_created) values(@q_desc,@sub_id,@marks,@userid,@cr_date);
        SET @q_id = IDENT_CURRENT('questions')

        INSERT INTO answers(ans_desc,q_id,created_by,DT_created,istrue)
            VALUES( @ans1,@q_id,@userid,@cr_date,
            CASE WHEN @ans1 =@ans THEN 1 ELSE 0 END);
    COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;
        DECLARE @ErrorLine INT;
        SELECT @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorLine=ERROR_LINE(),
        @ErrorState = ERROR_STATE();
        IF @@TRANCOUNT > 0 


    ROLLBACK TRANSACTION
        RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState,@ErrorLine);
    END CATCH
    END

ans i call this from my ASP.NET form as

AnsCmd is my stored procedure command...after adding all params

               try
                {


                    conn.Open();

                    AnsCmd.ExecuteNonQuery();

                    lblMsg.Visible = true;
                    lblMsg.Text = "success";
                    conn.Close();
                }
                catch (SqlException sqlex)
                {
                    lblMsg.Visible = true;
                    lblMsg.Text = sqlex.ToString();
                }

                catch (Exception ex)
                {
                    lblMsg.Visible = true;
                    lblMsg.Text = ex.ToString();

                }

to check that raiserror is working,i changed the table name in insert from answers to answers1 which does not exist..

when executed i get error message as

System.Data.SqlClient.SqlException: Invalid object name 'answers1'. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,......

so is this thing working properly or m i missing something???

Answer 1

The batch (stored procedure) is aborting when it gets to the non-existent table (which is deferred name resolution), so the ROLLBACK is not executing.

From MSDN/BOL:

Compile and Statement-level Recompile Errors

There are two types of errors that will not be handled by TRY…CATCH if the error occurs in the same execution level as the TRY…CATCH construct:

• Compile errors, such as syntax errors that prevent a batch from executing.
• Errors that occur during statement-level recompilation, such as object name resolution errors that happen after compilation due to deferred name resolution.

When the batch, stored procedure, or trigger that contains the TRY…CATCH construct generates one of these errors, the TRY…CATCH construct does not handle these errors.

I suggest you add SET XACT_ABORT ON at the top. This will force a ROLLBACK on error and "tidy up".

One more thing...

SET @q_id = IDENT_CURRENT('questions')

should be

SET @q_id = SCOPE_IDENTITY()

Edit:

CREATE PROCEDURE ans_insert
    @q_desc varchar(2000),
    @sub_id int,
    @marks int,
    @ans1 varchar(1000),
    @ans varchar(1000),
    @userid varchar(15),
    @cr_date datetime
AS
BEGIN

SET NOCOUNT, XACT_ABORT ON; -- what I do

BEGIN TRY
....

Answer 2

I don't think it'll affect the exception - but some thoughts:

• would SCOPE_IDENTITY() be easier (and more reliable) than IDENT_CURRENT? IDENT_CURRENT can return an id from another session during parallel operations. Also avoid @@IDENTITY, which can be impacted by triggers doing INSERTs
• why not let the calling (.NET) code worry about the transaction? It is far easier (and more versatile) to manage at a higher level, either on the connection (SqlTransaction) or wider (TransactionScope)

Example SqlTransaction approach:

using(SqlTransaction tran = conn.BeginTransaction()) {
    try {
        // operations (may need to set command.Transaction = tran)
        tran.Commit();
    } catch {
        tran.Rollback();
        throw;
    }
}

Example TransactionScope approach (** must SPAN the connection **)

using(TransactionScope tran = new TransactionScope()) {
    // operations: note no other changes
    tran.Complete();
}