Tag: sp

TSQL – Template for Stored Procedure

stored_procedure_template_TSQL

This file include a tsql stored procedure template, with :

  • Error catching
  • Log error in SQL Sever Log
  • Transaction management
-- =================================================
-- Best viewing if using tabs = 2 to your editor
-- =================================================
-- Author				:	Thierry Bertin
-- 							: www.abcdata.ca
-- Create date	: 2018-03-26
-- Description	: Template for TSQL Stored procedure
-- =================================================
create procedure dbo.table_name_get
(
	-- parameters ...
		@param_01																					varchar		(10)
	, @param_02																					varchar		(10)
	, @param_03																					integer						output
)
as
begin
	--	Suppress unwanted output
	set nocount on;
	
	--  Mandatory to manage SQL error loging						--	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  --  Flag for error catching
		set @error_point 	= '01';
		set @error_type 	= 'select';
		select					Col_01
									, Col_02
									, Col_03
									, ...
		from						dbo.table_name
		where						where_clause;

		--	Query 02  --  Flag for error catching
		set @error_point 	= '02';
		set @error_type 	= 'select';
		select					Col_01
									, Col_02
									, Col_03
									, ...
		from						dbo.table_name
		where						where_clause;
		
		--	Commit transaction
		if (@@trancount > 0)
		begin
			commit transaction;
		end
	end try
	begin catch
		--  Save error values
		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;

	--  Set error message, log and manage transaction
	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;

		--  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) with log;
		return (99);
	end
	
	--  Reset nocount option
	set nocount off

	--  End Stored procedure
	return (0);

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

Lien Permanent pour cet article : http://www.abcdata.ca/abc/?p=26