2 STORED PROCEDURE DONT WORKING

  • HELLO FRIENDS

    I HAVE 2 STORED PROCEDURES

    ONE IS FOR INSERTING ERROR MESSAGE TO ERROR TABLE

    AND SECOND SIMPLE INSERT PROCEDURE

    I WANT USE FIRST STORED PROCEDURE IN SECOND FOR INSERTING ERROR TO ERROR TABLE AND WANT TO SEE WITH OUTPUT

    MAX (ID) FROM ERROR TABLE WHEN WILL INSERT ERROR

    SECOND PROCEDURE INSERT TO TABLE SUCCES BUT DONT WORKING ERROR INSERTING(FIRST PROCEDURE)

     

    MY FIRST STORED PROC

     

    ALTER PROCEDURE [dbo].[Sola_uspLogError] 
    @ErrorLogID [int] = 0 OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON;

    SET @ErrorLogID = 0;

    BEGIN TRY

    IF ERROR_NUMBER() IS NULL
    RETURN;


    IF XACT_STATE() = -1
    BEGIN
    PRINT '!!!!!!!!!!!!!!! ';
    RETURN;
    END

    INSERT ERROR(E_NUMBER ,
    E_STATE ,
    E_SEVERITY,
    E_LINE ,
    E_PROCEDURE ,
    E_MESSAGE )
    VALUES(
    ERROR_NUMBER()
    ,ERROR_SEVERITY()
    ,ERROR_STATE()
    ,ERROR_PROCEDURE()
    ,ERROR_LINE ()
    ,ERROR_MESSAGE()
    )


    SET @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
    PRINT '???????? ';
    EXECUTE [dbo].[Sola_uspPrintError];
    RETURN -1;
    END CATCH
    END;

    AND SECOND PROCEDURES

    ALTER  PROCEDURE [dbo].[INSE] 
    @MENU_NAME NVARCHAR(5),
    @CAPTION NVARCHAR(5)
    AS
    BEGIN

    SET XACT_ABORT ON;
    BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO REG.MENU
    (MENU_NAME
    ,CAPTION
    )
    VALUES (@MENU_NAME, @CAPTION);

    COMMIT TRANSACTION;


    END TRY
    BEGIN CATCH

    EXECUTE [dbo].[Sola_uspLogError] /*usp_GetErrorInfo*/;

    IF (XACT_STATE()) = -1
    BEGIN
    PRINT
    N'The transaction is in an uncommittable state.' +
    'Rolling back transaction.'
    ROLLBACK TRANSACTION;
    END;

    IF (XACT_STATE()) = 1
    BEGIN
    PRINT
    N'The transaction is committable.' +
    'Committing transaction.'
    COMMIT TRANSACTION;
    END;
    END CATCH;
    END;
  • In [dbo].[Sola_uspLogError] , rather use

    SET @ErrorLogID = SCOPE_IDENTITY();

    In [dbo].[INSE] , the call to EXECUTE [dbo].[Sola_uspLogError] is before the ROLLBACK. This will cause the error information that was captured to be rolled back as well.

    In [dbo].[INSE] , you need to declare and use a variable to get the output from [dbo].[Sola_uspLogError]

    DECLARE @ErrorLogID int = 0;

    EXECUTE [dbo].[Sola_uspLogError] @ErrorLogID = @ErrorLogID OUTPUT;

    SELECT ErrorLogID = @ErrorLogID;
  • not working too

    because dont add to error table([dbo].[Sola_uspLogError] )

  • Does the REG.MENU table have a primary key?  If so wouldn't you want to output that from the procedure as well?

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • yes have primary key

    but i was deleted and tested

    also this error give me null from error table

    and i was update insert statement that in this stored proc (want to insert another simple table) and was create for delete procedure

    also give me return null and not inserted

    please code

    ---- this table i was create for inserting error
    create table test
    (id int not null identity(1,1),error_num int null)



    ALTER PROCEDURE [dbo].[Sola_uspLogError]
    @ErrorLogID [int] = 0 OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON;

    SET @ErrorLogID = 0;

    BEGIN TRY

    IF ERROR_NUMBER() IS NULL
    RETURN;


    IF XACT_STATE() = -1
    BEGIN
    PRINT '!!!!!!!!!!!!!!!!!!!!! ';
    RETURN;
    END

    INSERT into test(error_num )
    VALUES(
    ERROR_NUMBER()
    )


    SET @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
    PRINT '??????????????: ';
    EXECUTE [dbo].[Sola_uspPrintError];
    RETURN -1;
    END CATCH
    END;


    ----and this is delete procedure but not give me error
    ----give me also null with error and not inserted to table test

    ALTER PROCEDURE [dbo].[INSE_delete]

    @MENU_ID INT
    AS
    BEGIN

    SET XACT_ABORT ON;
    BEGIN TRY
    BEGIN TRANSACTION;

    delete from REG.MENU where MENU_ID=@MENU_ID;


    COMMIT TRANSACTION;


    END TRY
    BEGIN CATCH
    DECLARE @ErrorLogID int = 0;

    --EXECUTE [dbo].[Sola_uspLogError] @ErrorLogID = @ErrorLogID OUTPUT;

    --SELECT ErrorLogID = @ErrorLogID;
    EXECUTE [dbo].[Sola_uspLogError] /*usp_GetErrorInfo*/;

    IF (XACT_STATE()) = -1
    BEGIN
    PRINT
    N'The transaction is in an uncommittable state.' +
    'Rolling back transaction.'
    ROLLBACK TRANSACTION;
    END;

    IF (XACT_STATE()) = 1
    BEGIN
    PRINT
    N'The transaction is committable.' +
    'Committing transaction.'
    COMMIT TRANSACTION;
    END;
    END CATCH;
    END;
  • There are a bunch of issues with the code you've posted.  Have a look at this code (I don't have 2008 so please un-d.i.e the objects).  The 2 procs are consolidated into 1.  The errors are captured using a table valued function to insert into a procedure log table.  The insert to the log table must happen after the rollback (because otherwise the xact_state equals -1 which is "uncommittable").

    When m_id>0, e_id=0, when m_id=0, e_id>0.  The general OLTP idea is to return a confirmation of success, in this case m_id, to whatever is executing the procedure.

    To test the error capture, attempt to insert a duplicate menu_name.

    When you create the procedure to delete a single record (based on primary key) output a bit column-type equal to @@rowcount to confirm successful transaction.

    /* some test tables */
    drop table if exists test_menu;
    go
    create table test_menu(
    m_idint identity(1,1) constraint pk_menu primary key not null,
    menu_namenvarchar(50) unique not null,
    captionnvarchar(50) null);
    go

    drop table if exists test_procedure_log;
    go
    create table test_procedure_log(
    e_idint identity(1,1) constraint pk_procedure_log primary key not null,
    e_numberint not null,
    e_stateint not null,
    e_severityint not null,
    e_lineint not null,
    e_procedurenvarchar(128) not null,
    e_messagenvarchar(4000) not null,
    x_statesmallint not null);
    go

    /* table valued function to return error metadata */
    drop function if exists dbo.test_get_error_metadata;
    go
    create function dbo.test_get_error_metadata()
    returns table
    as
    return
    (select
    error_number() e_number,
    error_state() e_state,
    error_severity() e_severity,
    error_line () e_line,
    error_procedure() e_procedure,
    error_message() e_message,
    xact_state() x_state);
    go

    /* procedure to insert record and return m_id and e_id */
    drop proc if exists proc_insert;
    go
    create proc proc_insert
    @menu_namenvarchar(50),
    @captionnvarchar(50),
    @m_idint output,
    @e_idint output
    as
    begin
    set nocount on;
    set xact_abort on;

    begin transaction;
    begin try
    insert test_menu(menu_name, caption) values (@menu_name, @caption);
    select @m_id=cast(scope_identity() as int);
    select @e_id=0;
    commit transaction;
    end try
    begin catch
    if (xact_state()) = -1
    rollback transaction;

    insert test_procedure_log(e_number, e_state, e_severity, e_line, e_procedure, e_message, x_state)
    select * from dbo.test_get_error_metadata();
    select @e_id=cast(scope_identity() as int);
    select @m_id=0;
    end catch;
    end;

    set nocount off;
    set xact_abort off;
    go

    /* run the proc and view output variables */
    declare
    @test_m_idint,
    @test_e_idint;

    exec proc_insert '123', 'Lunch', @m_id=@test_m_id output, @e_id=@test_e_id output;

    if @test_m_id>0
    begin
    print ('Record was successsfully inserted. The m_id='+cast(@test_m_id as varchar(9)));
    --select * from test_menu;
    end
    else
    begin
    print ('Record was not inserted. The e_id='+cast(@test_e_id as varchar(9)));
    select * from test_procedure_log where e_id=@test_e_id;
    end

     

     

    • This reply was modified 4 years, 7 months ago by  Steve Collins.
    • This reply was modified 4 years, 7 months ago by  Steve Collins.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply