Create Stored Procedure Template

USE IPS
GO

IF EXISTS (select * from syscomments where id = object_id('dbo.uspMyLog') and texttype & 4 = 0)
BEGIN
    DROP PROCEDURE dbo.uspMyLog
    IF OBJECT_ID('dbo.uspMyLog') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.uspMyLog >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.uspMyLog >>>'
END 
GO

CREATE PROCEDURE [dbo].[uspMyLog]
(
	@vcParentSpName			VARCHAR(100),
	@vcSpName				VARCHAR(100),
	@vcMessage				VARCHAR(500),
	@tiLoggingLevel			TINYINT,		-- 1=fatal, 2=error, 3=warn, 4=info, 5=debug
	@tiConfigLoggingLevel	TINYINT = 2,	-- 1=fatal, 2=error, 3=warn, 4=info, 5=debug
	@dtProcDateTime			DATETIME2(0) = NULL,  -- Calling SP parent process starting date and time
	@iSqlSeverity			INTEGER = NULL,
	@vcInstance				VARCHAR(50) = NULL
)
AS
/***********************************************************************************

 Procedure    :   dbo.uspMyLog
 Version      :   1.00
 
 Created      :   01/01/2020
 Author       :   Author
 
 Description  :   Description

 Syntax       :   Syntax

 Modification History

 Request    Date        Name                Description
 ---------  ----------  ------------------  -----------------------------------
 JIRA-123	01/01/2020  Author		        Initially created

************************************************************************************/
BEGIN

	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	SET NOCOUNT ON;
	
	DECLARE @dtCurrentDateTime		DATETIME2(0) = SYSDATETIME();
	
	IF(@dtProcDateTime IS NULL)
	BEGIN
		SET @dtProcDateTime = @dtCurrentDateTime;
	END;

	IF(@tiConfigLoggingLevel >= @tiLoggingLevel)
	BEGIN
		INSERT INTO dbo.tblMyLog
		(
			LogLevel,
			vcParentSpName,
			vcSpName,
			dtCreateDateTime,
			vcMessage,
			dtProcDateTime,
			iSqlSeverity,
			vcInstance
		)
		VALUES
		(
			@tiLoggingLevel,
			@vcParentSpName,
			@vcSpName,
			@dtCurrentDateTime,
			@vcMessage,
			@dtProcDateTime,
			@iSqlSeverity,
			@vcInstance
		);
	END;
	RETURN 0;      -- OK
	
END

GO
/* *** ************************************************************************************ *** */
GO
DECLARE @nvcObjectName SYSNAME = N'dbo.uspMyLog';

IF OBJECT_ID(@nvcObjectName) IS NOT NULL
	RAISERROR('<<< CREATED PROCEDURE %s >>>', 0, 1,  @nvcObjectName);
ELSE
	RAISERROR('<<< FAILED CREATING PROCEDURE %s >>>', 16, 1,  @nvcObjectName);
GO
/* *** ************************************************************************************ *** */
GO
GRANT EXECUTE ON dbo.uspMyLog TO MyRole;
GO
/* *** ************************************************************************************ *** */
GO