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