SQL server 存储过程模板

存储过程直接套用模板、
流程控制、
执行日志、
根据实际情况选择、

CREATE PROCEDURE [dbo].[procProcTemplete]
(
	@a INT, -- 入参
	@b INT OUTPUT --出参
)
AS
BEGIN
	SET NOCOUNT ON;

	--变量定义
	--计算过程执行时长
	DECLARE @starttime DATETIME
	DECLARE @totaltime INT
	--流程控制(成功失败)
	DECLARE @Result INT
	--执行影响行数
	DECLARE @ROW INT
	--错误日志
	DECLARE @ErrorMessage VARCHAR(1000)

	--变量赋值
	SET @starttime = GETDATE()
	SET @Result = 0
	
	--创建临时表
	CREATE TABLE #tmp_aa
	(Id INT,
	 Value VARCHAR(50))

	 --处理临时表数据
	 INSERT INTO #tmp_aa
	         ( Id, Value )
	 VALUES  ( 0, -- Id - int
	           'AAA'  -- Value - varchar(50)
	           )

	 --开启事务
	 BEGIN TRAN
	 BEGIN TRY

	 --计算逻辑
	 IF EXISTS(SELECT 1 FROM #tmp_aa
	 WHERE Id = @a)
	 BEGIN 

	 SET @b = 1
	 SET @Result = 0
	 SET @ROW = @@ROWCOUNT
	 
	 END	
	 ELSE 
	 BEGIN 
	 SET @Result = 1
	 END

	 --计算总时长
	 SET @totaltime=datediff(second,@starttime,getdate());

	 END TRY
	 BEGIN CATCH

	IF @@TRANCOUNT > 0
	BEGIN
		ROLLBACK TRAN
	SELECT @ErrorMessage='操作发生异常:'+CAST(ERROR_NUMBER() AS VARCHAR)+','+ERROR_MESSAGE(),@Result = -1
	END

	END CATCH

	IF @@TRANCOUNT > 0 AND @Result = 0
	BEGIN
		COMMIT TRAN;
	END

	IF @@TRANCOUNT > 0 AND @Result <> 0
	BEGIN
		ROLLBACK TRAN;
	END

	--SELECT @b AS b;

	--删除临时表
	IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmp_aa'))
	BEGIN
	DROP TABLE #tmp_aa
	END

	--记录日志
	INSERT INTO tb_SYSLOGS(Logger,Logtime,LogIP,Logname,LogTotalTime,LevelCode,Message,LogSQL)
	SELECT 'Templete',GETDATE(),'::1','sa',@totaltime,'procProcTemplete','','影响行数' + CAST(@ROW AS VARCHAR(10)) + ',' + @ErrorMessage;

	END

---

存储过程日志表：tb_SYSLOGS

CREATE TABLE [dbo].[tb_SYSLOGS](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[Logger] [VARCHAR](50) NULL,
	[Logtime] [DATETIME] NOT NULL CONSTRAINT [DF_tb_SYSLOGS_Logtime]  DEFAULT (GETDATE()),
	[LogIP] [VARCHAR](50) NULL,
	[Logname] [VARCHAR](50) NULL,
	[LogTotalTime] [INT] NULL,
	[LevelCode] [VARCHAR](50) NULL,
	[Message] [VARCHAR](MAX) NULL,
	[LogSQL] [VARCHAR](MAX) NULL,
 CONSTRAINT [PK_tb_SYSLOGSId] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SYSLOGS', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'操作人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SYSLOGS', @level2type=N'COLUMN',@level2name=N'Logger'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'操作时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SYSLOGS', @level2type=N'COLUMN',@level2name=N'Logtime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对应ip' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SYSLOGS', @level2type=N'COLUMN',@level2name=N'LogIP'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对应mssql登录名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SYSLOGS', @level2type=N'COLUMN',@level2name=N'Logname'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'耗时' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SYSLOGS', @level2type=N'COLUMN',@level2name=N'LogTotalTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'等级' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SYSLOGS', @level2type=N'COLUMN',@level2name=N'LevelCode'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日志信息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SYSLOGS', @level2type=N'COLUMN',@level2name=N'Message'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'执行脚本' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SYSLOGS', @level2type=N'COLUMN',@level2name=N'LogSQL'
GO