Tag: tsql

Stored Procedure Template, Azure version

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
-----------------------------------------------------------------------------------------------------------------------

Lien Permanent pour cet article : https://www.abcdata.ca/abc/?p=112

TSQL – Listing SQL Objects by Type

  • Classé dans TSQL

  • 2018/03/28

  • 2018/03/28

database_object_by_type_get_TSQL

A simple script to list sql objects by types :

-- =================================================
-- Best viewing if using tabs = 2 to your editor
-- =================================================
-- Author				:	Thierry Bertin
-- 							: www.abcdata.ca
-- Create date	: 2018-03-28
-- Description	: Get SQL Objects by Type
-- =================================================
select						type 
								,	name
from							sys.objects
where							type	 in (
																	'AF'		--	Aggregate function (CLR)
																,	'C'			--	CHECK constraint
																,	'D'			--	DEFAULT (constraint or stand-alone) 
																, 'F'			--	FOREIGN KEY constraint 
																, 'FN'		--	SQL scalar function 
																, 'FS'		--	Assembly (CLR) scalar-function 
																, 'FT'		--	Assembly (CLR) table-valued function 
																, 'IF'		--	SQL inline table-valued function
																, 'IT'		--	Internal table
																, 'P'			--	SQL Stored Procedure
																, 'PC'		--	Assembly (CLR) stored-procedure
																, 'PG'		--	Plan guide
																,	'PK'		--	PRIMARY KEY constraint
																, 'R'			--	Rule (old-style, stand-alone)
																, 'RF'		--	Replication-filter-procedure
																,	'S'			--	System base table
																, 'SN'		--	Synonym
																, 'SO'		--	Sequence object
																, 'SQ'		--	Service queue
																,	'TA'		--	Assembly (CLR) DML trigger
																,	'TF'		--	SQL table-valued-function
																,	'TR'		--	SQL DML trigger
																, 'TT'		--	Table type	
																, 'U'			--	Table (user-defined)
																, 'UQ'		--	UNIQUE constraint
																, 'V'			--	View
																,	'X'			--	Extended stored procedure
															)
order by					type , name;

Lien Permanent pour cet article : https://www.abcdata.ca/abc/?p=44

TSQL – Template for Stored Procedure

  • Classé dans TSQL

  • 2018/03/28

  • 2018/03/28

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 : https://www.abcdata.ca/abc/?p=26