--drop object if it already exists
if exists (select * from dbo.sysobjects where id = object_id(N' proc_template') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure proc_template
go
--
create procedure proc_template @in_test varchar(10) = null, @in_force_rollback varchar(1) = n
as
begin
declare
@v_totalrows int,
@v_procedurename varchar(100),
@v_proc_timestart datetime,
@v_errormessage varchar(8000),
@v_process_id int,
@v_current_id int,
@v_success varchar(1),
@v_message varchar(200),
@v_error_count int,
@v_total_success_rows int,
@v_total_fail_rows int,
@v_out_success numeric,
@v_invalid_param_flag int,
@v_rollback_flag varchar(1),
@v_directory varchar(2000),
@v_test int
--
declare @log_temp table
(
logid int,
parentlogid int,
logtype varchar(20),
contextname varchar(255),
referenceid int,
referencename varchar(255),
started datetime,
completed datetime,
success varchar(1),
message varchar(4000),
total int,
passed int,
failed int,
ignored int,
latestrun varchar(1)
)
--
-- explicity suppress count information
--
set nocount on
--
-- get object id of procedure and initialise counter
--
set @v_procedurename = object_name(@@procid)
set @v_totalrows = 0
set @v_current_id = 1
set @v_process_id = @v_current_id
set @v_proc_timestart = getdate()
set @v_rollback_flag = @in_force_rollback
--
-- log the start of the procedure
--
insert into @log_temp
(logid,
parentlogid,
logtype,
contextname,
referenceid,
referencename,
started,
completed,
success,
message,
total,
passed,
failed,
ignored,
latestrun
)
values
(@v_process_id ,
null,
'process',
@v_procedurename,
null,
'row',
@v_proc_timestart,
null,
null,
null,
null,
null,
null,
null,
'y'
)
--
begin try
begin transaction
begin try
--
-- main procedure code begin here
--
-- depending on the parameter passed to the proc the following line will pass/fail and demonstrate rollback
--
set @v_test = @in_test
--
-- check if input directory exists via @p_path in out parameter commented out as get_directory released separately
--
--execute dbo.get_directory @p_name = @v_procedurename, @p_path = @v_directory out
--
--if @v_directory = 'directory_not_found'
--begin
--print 'directory not found'
--
-- set rollback flag if rollback is required for a transaction which would normally commit, i.e. failure of business logic validation
-- requires a rollback
-- uncommitted transactions resulting from a sqlserver error will be handled after try catch blocks and automatically rolled back
--set @v_rollback_flag = 'y'
--raiserror('directory name provided does not exist in column name table directories',16,1)
--end
--
end try
begin catch
set @v_errormessage = @v_procedurename + ' : ' + error_message()
print 'inner catch'
set @v_current_id = @v_current_id + 1
--
-- set rollback flag if rollback is required for a transaction which would normally commit, i.e. failure of business logic validation
-- requires a rollback
-- uncommitted transactions resulting from a sqlserver error will be handled after try catch blocks and automatically rolled back
--
--set @v_rollback_flag = 'y'
--
-- insead of logging to log we want to log to the table in memory
--
insert into @log_temp
(logid,
parentlogid,
logtype,
contextname,
referenceid,
referencename,
started,
completed,
success,
message,
total,
passed,
failed,
ignored,
latestrun
)
select @v_current_id,
@v_process_id,
'error',
contextname,
referenceid,
referencename,
getdate(),
null,
success,
@v_errormessage,
total,
passed,
failed,
ignored,
null
from @ log_temp
end catch
--
-- depending on the parameter passed to the proc the following line will pass/fail and demonstrate rollback
--
set @v_test = @in_test
end try
begin catch
set @v_errormessage = @v_procedurename + ' : '+ error_message()
print 'outer catch'
set @v_current_id = @v_current_id + 1
--
-- set rollback flag if rollback is required for a transaction which would normally commit, i.e. failure of business logic validation
-- requires a rollback
-- uncommitted transactions resulting from a sqlserver error will be handled after try catch blocks and automatically rolled back
--
--set @v_rollback_flag = 'y'
--
-- insead of logging to log we want to log to the table in memory
--
insert into @log_temp
(logid,
parentlogid,
logtype,
contextname,
referencename,
started,
completed,
message
)
select @v_current_id,
@v_process_id,
'error',
@v_procedurename,
'row',
getdate(),
null,
@v_errormessage
--
end catch
--
-- check state of transaction and rollback flag and handle accordingly
--
print 'rollback flag - ' + @v_rollback_flag
print 'xact state - ' + convert(varchar,xact_state())
if (xact_state() = -1 or (@v_rollback_flag = 'y' and xact_state() != 0))
begin
rollback transaction
set @v_current_id = @v_current_id + 1
print 'rollback transaction'
insert into @ log_temp
(logid,
parentlogid,
logtype,
contextname,
referencename,
started,
completed,
message
)
select @v_current_id,
@v_process_id,
'info',
@v_procedurename,
'row',
getdate(),
null,
'procedure rolled back'
end
else
begin
commit transaction
print 'commit transaction'
end
--
-- handle error logging tables
-- no other functionality in this section
--
--
-- set to successful by default
--
set @v_success = 'y'
set @v_message = 'completed successfully'
--
-- check if any errors were logged
--
select @v_error_count = count (*)
from @log_temp
where upper(logtype) = 'error'
--
if(@v_error_count>0)
begin
set @v_success = 'n'
set @v_message = 'failed with errors'
set @v_out_success = 1
end
--
-- log end of procedure with failure or success
--
update @log_temp
set failed = 0,
total = 0,
passed = 0,
ignored = 0,
success = @v_success,
completed = getdate(),
message = @v_message
where logid = (select min(logid)
from @log_temp)
--
select * from @log_temp
--
-- output table variables back into the real tables
--
insert into log
(logid,
parentlogid,
logtype,
contextname,
referenceid,
referencename,
started,
completed,
success,
message,
total,
passed,
failed,
ignored,
latestrun
)
select logid + isnull((select max(logid)
from log),0),
parentlogid + isnull((select max(logid)
from log),0),
logtype,
contextname,
referenceid,
referencename,
started,
completed,
success,
message,
total,
passed,
failed,
ignored,
latestrun
from @log_temp
--
-- maintain latestrun column
--
update log
set latestrun = null
where contextname = @v_procedurename
and started < @v_proc_timestart
--
--
end
No comments:
Post a Comment