set nocount on
create table procedures
(
	id int identity not null primary key,
	catalog_id int not null default(1), -- references catalogs(id)
	proc_type_id int not null default(1), -- references proc_types(id) sp/job/fn/dts/exe
	proc_name sysname not null,
	proc_desc varchar(256),
	unique(catalog_id,proc_type_id,proc_name)
)

create table log_procedure
(
	id int identity not null primary key,
	procedure_id int not null references procedures(id),
	start datetime not null default(getdate()),
	finish datetime null,
	errorcode int null
)

create table log_proc_message
(
	id int not null identity primary key,
	log_procedure_id int not null references log_procedure(id),
	msg_date datetime not null default(getdate()),
	message varchar(256) not null
)

insert procedures(proc_name,proc_desc)
values('testlogging','just for testing some concepts...')
go

-- A script creates the skeleton procs/jobs
-- exec spCreateProc @id = 1

create procedure testlogging as
set nocount on

declare @procedure_id int, @log_procedure_id int
set @procedure_id = 1	-- this is my hardcoded id in procedures table

-- now I start executing
insert log_procedure(procedure_id) values(@procedure_id)
set @log_procedure_id = scope_identity()
insert log_proc_message(log_procedure_id,message) values(@log_procedure_id,'START EXECUTION : testlogging')

-- Now I write some messages during execution
insert log_proc_message(log_procedure_id,message) values(@log_procedure_id,'testlogging is executing')
	declare @t char(8)
	set @t = '00:00:0' + cast(2+datepart(s,getdate())%6 as char(1))	-- wait 2-8 secs
	waitfor delay @t
insert log_proc_message(log_procedure_id,message) values(@log_procedure_id,'testlogging inserted n rows into table x')
insert log_proc_message(log_procedure_id,message) values(@log_procedure_id,'testlogging has completed it''s job succesfully')

-- Now I am finished
insert log_proc_message(log_procedure_id,message) values(@log_procedure_id,'END EXECUTION : testlogging')
update log_procedure set finish = getdate(), errorcode = 0
where id = @log_procedure_id

return 0
go


-- execute the proc a few times
exec testlogging
exec testlogging
exec testlogging

-- view the log...
select
	left(p.proc_name,20) as proc_name,
	lpm.msg_date,
	case 	when lpm.message like('START%') then lpm.message
		when lpm.message like('END%') then lpm.message + ' EXECTIME: ' + ltrim(datediff(second,start,finish)) + ' seconds'
		else char(9) + lpm.message end as message
from
	procedures p
	join log_procedure lp on p.id = lp.procedure_id
	join log_proc_message lpm on lp.id = lpm.log_procedure_id
order by
	lpm.id desc

select * from procedures
select * from log_procedure order by id
select * from log_proc_message order by id

-- cleanup
drop table log_proc_message
drop table log_procedure
drop table procedures
drop procedure testlogging