IF OBJECT_ID('dbo.DBA_proc_log') IS NULL
BEGIN
CREATE TABLE dbo.DBA_proc_log(
logId BIGINT IDENTITY(1,1) PRIMARY KEY ,
procName VARCHAR(50) NOT NULL,
beginTime DATETIME NOT NULL DEFAULT(GETDATE()),
endTime DATETIME NOT NULL DEFAULT('1900-01-01'),
elapsedSeconds AS CASE WHEN endTime<beginTime THEN -1 ELSE DATEDIFF(second,beginTime,endTime) END,
errMsg NVARCHAR(MAX) NOT NULL DEFAULT(''),
succeeded AS CASE WHEN endTime<beginTime OR errMsg!='' THEN 0 ELSE 1 END,
checked BIT NOT NULL DEFAULT(0)
)
END
--示例存储过程
CREATE PROC proc_test
AS
BEGIN
SET NOCOUNT ON
--插入日志
DECLARE @logId BIGINT
INSERT INTO dbo.DBA_proc_log (procName) VALUES ('proc_test') --注意修改为当前存储过程名
SET @logId=@@IDENTITY;
--存储过程原执行部分
--设置日志结束
UPDATE dbo.DBA_proc_log SET endTime=GETDATE() WHERE logId=@logId
END
GO
EXEC proc_test
SELECT * FROM dbo.DBA_proc_log AS dpl
存储过程日志表
最新推荐文章于 2023-03-31 15:19:55 发布