SQL server 存储过程模板

骑猪看日落 2022-05-20 01:46 214阅读 0赞

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

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