IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'wm_ErrorLog')
DROP TABLE [wm_ErrorLog]
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'WMSp_ErrorLog_Ins_Error' AND type ='P')
DROP PROCEDURE [WMSp_ErrorLog_Ins_Error]
CREATE TABLE [wm_ErrorLog] (
    [Error_id] INT IDENTITY(1,1)
	,[Error_Number] [INT] NOT NULL
	,[Error_procedure] VARCHAR(max) NOT NULL
	,[Error_Message] [VARCHAR](MAX) NULL
	,[Error_Line] INT
	,[Error_State] INT 
	,[Error_Severity] INT 
	,[SPID] [INT] NULL
	,[Program_Name] [VARCHAR](255) NULL
	,[Client_Address] [VARCHAR](255) NULL
	,[Authentication] [VARCHAR](50) NULL
	,[Error_User_Application] [VARCHAR](100) NULL
	,[Error_Date] [DATETIME] NULL
	,[Error_User_System] [sysname] NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE wm_ErrorLog ADD CONSTRAINT [dfltErrorLog_error_date] DEFAULT(GETDATE())
FOR [Error_Date]
GO

ALTER TABLE wm_ErrorLog ADD CONSTRAINT [dfltErrorLog_error_user_system] DEFAULT(SUSER_SNAME())
FOR [Error_User_System]
GO

ALTER TABLE [wm_ErrorLog] ADD CONSTRAINT wmpk_wm_errorlog_id PRIMARY KEY (error_id)

GO


Create  PROCEDURE [WMSp_ErrorLog_Ins_Error] (
	 @Error_Number INT = NULL
	,@Error_procedure SYSNAME = 'Ad hoc sql'
	,@Error_Message VARCHAR(4000) = NULL
	,@UserID INT = NULL
	)
AS
BEGIN
	BEGIN TRY
		INSERT INTO wm_ErrorLog (
			[Error_Number]
			,[Error_procedure]
			,[Error_Message]
			,[Error_line]
			,[Error_state]
			,[Error_severity]
			,[SPID]
			,[Program_Name]
			,[Client_Address]
			,[Authentication]
			,[Error_User_System]
			,[Error_User_Application]
			)
		SELECT [Error_Number] = ISNULL(@Error_Number, ERROR_NUMBER())
			,[Error_procedure] = COALESCE(ERROR_PROCEDURE(),@Error_procedure)
			,[Error_Message] = ISNULL(@Error_Message, ERROR_MESSAGE())
			,[Error_line] = ERROR_LINE()
			,[Error_state] =ERROR_STATE()
			,[Error_severity] = ERROR_SEVERITY()
			,[SPID] = @@SPID
			,[Program_Name] = ses.program_name
			,[Client_Address] = con.client_net_address
			,[Authentication] = con.auth_scheme
			,[Error_User_System] = SUSER_SNAME()
			,[Error_User_Application] = @UserID
		FROM sys.dm_exec_sessions ses 
		LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
		WHERE ses.session_id= @@SPID
	END TRY

	BEGIN CATCH
		-- We even failed at the log entry so let's get basic  
		INSERT INTO wm_ErrorLog (
			ERROR_NUMBER
			,ERROR_procedure
			,ERROR_MESSAGE
			)
		VALUES (
			- 100
			,OBJECT_NAME(@@PROCID)
			,ERROR_MESSAGE()
			)
	END CATCH
END
GO