August 22, 2019 at 7:12 pm
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
N'The transaction is in an uncommittable state.' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
IF (XACT_STATE()) = 1
BEGIN
N'The transaction is committable.' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
END;
August 22, 2019 at 8:13 pm
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;
August 22, 2019 at 8:44 pm
not working too
because dont add to error table([dbo].[Sola_uspLogError] )
August 22, 2019 at 9:06 pm
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
August 22, 2019 at 9:15 pm
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
N'The transaction is in an uncommittable state.' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
IF (XACT_STATE()) = 1
BEGIN
N'The transaction is committable.' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
END;
August 22, 2019 at 11:45 pm
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
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