Tag: Azure

azure_stored_procedure_template

Because SQL Server version on Azure platform doesn’t allow to write in sql server log, the template for stored procedure with sql errors catching is different.

In place off writing sql error message on sql server log, the sp write into an audit table.

Create Audit.Error_Log table

-- ==============================================
-- Author				:	Thierry Bertin - www.abcdata.ca
-- Create date	: 2018-04-27
-- Description	: Create Table Audit.Error_Log
-- ==============================================
create table Audit.Error_Log
(
		Error_Log_Id					integer 	identity (1, 1)					not null
 , 	Point       					varchar 	(2) 										not nulL
 , 	Type        					varchar 	(20) 										not nuLL
 , 	Number      					integer 													not null
 , 	Severity    					integer 													not null
 , 	State       					integer 													not null
 , 	Procedure   					varchar 	(50) 										not nuLL
 , 	Line        					varchar 	(50) 										not nuLL
 , 	Message     					varchar 	(500) 									not nULL
 , 	xact_State  					integer 													not null
 , 	Upd_DT      					datetime 													not null
 , 	constraint	pk_error_log	primary key clustered (error_log_id	asc)
);
go
-----------------------------------------------------------------------------------------------------------------------

Create Audit.Error_Log_Add stored procedure
-- ==============================================
-- Author				:	Thierry Bertin - www.abcdata.ca
-- Create date	: 2018-04-19
-- Description	: Insert into Audit.Error_Log
-- ==============================================
create procedure [Audit].Error_Log_Add
(
		@error_point																			varchar		(2)
	, @error_type																				varchar		(20)
	, @error_number																			integer
	, @error_severity																		integer
	, @error_state																			integer
	, @error_procedure																	varchar 	(50)
	, @error_line																				varchar 	(50)
	, @error_message																		varchar 	(500)
	, @xact_state																				integer
)
as
begin
	--	Suppress unwanted output
	set nocount on;

	insert
	into						[Audit].Error_Log
								(
									point
								,	type
								, number
								, severity
								, state
								, [procedure]
								, line
								, message
								, xact_state
								, Upd_DT
								)
	values				(
									@error_point
								, @error_type
								, @error_number
								, @error_severity
								, @error_state
								, @error_procedure
								, @error_line
								, @error_message
								, @xact_state
								, getdate ()
								);

	--  Reset nocount option
	set nocount off

	--  End Stored procedure
	return (0);

end
go
-----------------------------------------------------------------------------------------------------------------------

Create Audit.Error_Lst stored procedure to list errors log between two dates
-- ==============================================
-- Author				:	Thierry Bertin - www.abcdata.ca
-- Create date	: 2018-04-19
-- Description	: Template SP Azure like
-- ==============================================
create procedure Audit.Error_Log_Lst
(
		@Begin_DT																					datetime					= null
	, @End_DT																						datetime					= null
)
as
begin
	--	Suppress unwanted output
	set nocount on;

	--  Mandatory 																			--	Don't delete  --
	declare @error_point																varchar		(2);
	declare	@error_type																	varchar		(20);
	declare	@error_data																	varchar		(1000);
	declare	@error_number																integer;
	declare	@error_severity															integer;
	declare	@error_state																integer;
	declare	@error_procedure														varchar 	(50);
	declare	@error_line																	varchar 	(50);
	declare	@error_message															varchar 	(500);
	declare @xact_state																	integer;

	--	Working Variables

	begin try
		--	Initialize a transaction
		begin transaction;

		--	Initialize Variables
		if (@Begin_DT is null)
		begin
			set @Begin_DT = dateadd (day , -5 , getdate ());
		end

		if (@End_DT is null)
		begin
			set @End_DT = dateadd (day , +1 , getdate ());
		end

		--	Query 01
		set @error_point 	= '01';
		set @error_type 	= 'select';
		select					Point
									, Type
									, Number
									, Severity
									, State
									, [Procedure]
									, Line
									, Message
									, xact_state
									, Upd_DT
		from						Audit.Error_Log
		where						Upd_DT between @Begin_DT and @End_DT
		order by				Upd_DT desc;

		--	Commit transaction
		if (@@trancount > 0)
		begin
			commit transaction;
		end
	end try
	begin catch
		select					@error_number 		= error_number()
									, @error_severity 	= error_severity()
									, @error_state 			= error_state()
									, @error_procedure 	= error_procedure()
									, @error_line 			= error_line()
									, @error_message 		= error_message()
									, @xact_state 			= xact_state();
	end catch;

	--  Error on select
	if (@error_number <> 0)
	begin
		set @error_data 		= 'error on '					+ @error_type
												+ ' / error code = ' 	+ cast (@error_number 		as varchar (10))
												+ ' / severity = ' 		+ cast (@error_severity 	as varchar (10))
												+ ' / state = ' 			+ cast (@error_state 			as varchar (10))
												+ ' / xact_state = ' 	+ cast (@xact_state 			as varchar (10))
												+ ' / procedure = ' 	+ @error_procedure
												+ ' / line = ' 				+ cast (@error_line 			as varchar (10))
												+ ' / message = "' 		+ @error_message + '"';

		set @error_data 	= 'SP Error at query : ' + @error_point + ' - ' + @error_data;

		execute Audit.Error_Log_Add @error_point , @error_type , @error_number , @error_severity , @error_state , @error_procedure , @error_line , @error_message , @xact_state;

		--  Reset nocount option
		set nocount off

		--	Rollback transaction
		if (@@trancount > 0)
		begin
			if (@xact_state	=	-1)
			begin
				rollback transaction;
			end
			else
			begin
				if	(@xact_state = 1)
				begin
					commit transaction;
				end
			end
		end

		--  Stop SP and write error message into sql server log
		raiserror (@error_data , 16 , 1);
		return (99);
	end

	--  Reset nocount option
	set nocount off

	--  End Stored procedure
	return (0);

end
go
-----------------------------------------------------------------------------------------------------------------------

And the stored procedure template
-- ==============================================
-- Author				:	Thierry Bertin - www.abcdata.ca
-- Create date	: 2018-04-19
-- Description	: Template SP Azure like
-- ==============================================
create procedure Schema.SP_Name_Add (Get / Lst / Set / Del)
(
	--	insert parameters ...
)
as
begin
	--	Suppress unwanted output
	set nocount on;

	--  Mandatory 																			--	Don't delete  --
	declare @error_point																varchar		(2);
	declare	@error_type																	varchar		(20);
	declare	@error_data																	varchar		(1000);
	declare	@error_number																integer;
	declare	@error_severity															integer;
	declare	@error_state																integer;
	declare	@error_procedure														varchar 	(50);
	declare	@error_line																	varchar 	(50);
	declare	@error_message															varchar 	(500);
	declare @xact_state																	integer;

	--	Working Variables

	begin try
		--	Initialize a transaction
		begin transaction;

		--	Initialize Variables

		--	Query 01
		set @error_point 	= '01';
		set @error_type 	= 'insert';
		--	insert query here

		--	Commit transaction
		if (@@trancount > 0)
		begin
			commit transaction;
		end
	end try
	begin catch
		select					@error_number 		= error_number()
									, @error_severity 	= error_severity()
									, @error_state 			= error_state()
									, @error_procedure 	= error_procedure()
									, @error_line 			= error_line()
									, @error_message 		= error_message()
									, @xact_state 			= xact_state();
	end catch;

	--  Error on select
	if (@error_number <> 0)
	begin
		set @error_data 		= 'error on '					+ @error_type
												+ ' / error code = ' 	+ cast (@error_number 		as varchar (10))
												+ ' / severity = ' 		+ cast (@error_severity 	as varchar (10))
												+ ' / state = ' 			+ cast (@error_state 			as varchar (10))
												+ ' / xact_state = ' 	+ cast (@xact_state 			as varchar (10))
												+ ' / procedure = ' 	+ @error_procedure
												+ ' / line = ' 				+ cast (@error_line 			as varchar (10))
												+ ' / message = "' 		+ @error_message + '"';

		set @error_data 	= 'SP Error at query : ' + @error_point + ' - ' + @error_data;

		execute Audit.Error_Log_Add @error_point , @error_type , @error_number , @error_severity , @error_state , @error_procedure , @error_line , @error_message , @xact_state;

		--  Reset nocount option
		set nocount off

		--	Rollback transaction
		if (@@trancount > 0)
		begin
			if (@xact_state	=	-1)
			begin
				rollback transaction;
			end
			else
			begin
				if	(@xact_state = 1)
				begin
					commit transaction;
				end
			end
		end

		--  Stop SP and write error message into sql server log
		raiserror (@error_data , 16 , 1);
		return (99);
	end

	--  Reset nocount option
	set nocount off

	--  End Stored procedure
	return (0);

end
go
-----------------------------------------------------------------------------------------------------------------------