T-SQL存储过程模板（个人用）

本文个人日常使用整理，不喜请绕路，指正请联系。

1.存储过程

CREATE PROCEDURE ${PROC_NAME}(
	@ID INT,
	@UserID INT,
	@CallType INT, --用于判断是检查还是执行
	@RetVal NVARCHAR(200) OUTPUT
)
AS
/*
    ========================================================================
	Stored Procedure Name:  
	Description:            
	Created Date:           ${DATE} ${TIME}
	Created By:             Justin Han(韩辉)
	Version:                1.0
    Modified Date:          
    Modified By:            
    Modification Details:   
    Input Parameters:       @ID INT   -- xxx ID
                            @UserID INT 当前用户的EmpID
                            @CallType INT 用于针对预处理或执行或后处理，通常预处理用1，执行用2，预处理用3来进行标识，如没有检查，可以去除这个参数
    Output Parameters:      
    Error Handling:         如果有错误，错误信息记录在[su_ProcExecErrorInfo]表中,@SqlText可用来记录关键或易错步骤，用于后续查询跟踪
    ========================================================================        
*/
BEGIN
	SET @RetVal = 0
	DECLARE @SqlText NVARCHAR(MAX) = ''
	SET NOCOUNT ON
	
	-----------------------------------------------------业务检查开始-----------------------------------------------------
	IF @CallType = 1 BEGIN
		--Error级别
		SET @RetVal = 111
		RETURN
		--Confirm级别
		
		--Warming级别
	END
	-----------------------------------------------业务检查开始----------------------------------------------
	
	-----------------------------------------------业务处理开始----------------------------------------------
	IF @CallType = 3 BEGIN
		SET XACT_ABORT ON
		BEGIN TRY
			BEGIN TRANSACTION
				
				--实际业务处理过程
				-- 1. 记录当前操作
				SET @SqlText = 'Step 1: Initializing data'
			COMMIT TRANSACTION
		END TRY
		BEGIN CATCH
			--回滚事务
			ROLLBACK TRANSACTION
			SET @RetVal = -1
			
			--记录错误信息
			INSERT INTO su_ProcExecErrorInfo
			(
				ProcName, InParam, ExceptionTime, ErrorMsg, ErrorNumber, ErrorSeverity, ErrorState, ErrorLine, SqlText
			)
			VALUES
				(
					'sp_Name',
					CONCAT('@ID=', @ID, ', @UserID=', @UserID, ', @CallType=', @CallType),
					GETDATE(),
					ERROR_MESSAGE(),
					ERROR_NUMBER(),
					ERROR_SEVERITY(),
					ERROR_STATE(),
					ERROR_LINE(),
					@SqlText
				)
			RETURN
		END CATCH
	END
END
GO

2.记录表单

IF OBJECT_ID('su_ProcExecErrorInfo') IS NOT NULL DROP TABLE su_ProcExecErrorInfo
GO

CREATE TABLE su_ProcExecErrorInfo
(
    ErrorID INT IDENTITY(1,1) PRIMARY KEY,
    ProcName NVARCHAR(200),
    InParam NVARCHAR(MAX),
    ExceptionTime DATETIME,
    ErrorMsg NVARCHAR(MAX),
    ErrorNumber INT,
    ErrorSeverity INT,
    ErrorState INT,
	ErrorLine INT,
    SqlText NVARCHAR(MAX)  -- 用于存储出错时的 SQL 查询
)
Go

-- 添加表的注释
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'存储过程执行异常信息记录', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
	 @level1name = N'su_ProcExecErrorInfo';

-- 为列添加注释
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'自增主键', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
     @level1name = N'su_ProcExecErrorInfo', @level2type = N'COLUMN', @level2name = N'ErrorID';

EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'存储过程名称', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
     @level1name = N'su_ProcExecErrorInfo', @level2type = N'COLUMN', @level2name = N'ProcName';

EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'存储过程输入参数', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
     @level1name = N'su_ProcExecErrorInfo', @level2type = N'COLUMN', @level2name = N'InParam';

EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'异常发生时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
     @level1name = N'su_ProcExecErrorInfo', @level2type = N'COLUMN', @level2name = N'ExceptionTime';

EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'异常信息', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
     @level1name = N'su_ProcExecErrorInfo', @level2type = N'COLUMN', @level2name = N'ErrorMsg';

EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'错误号', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
     @level1name = N'su_ProcExecErrorInfo', @level2type = N'COLUMN', @level2name = N'ErrorNumber';

EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'严重性', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
     @level1name = N'su_ProcExecErrorInfo', @level2type = N'COLUMN', @level2name = N'ErrorSeverity';

EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'状态', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
     @level1name = N'su_ProcExecErrorInfo', @level2type = N'COLUMN', @level2name = N'ErrorState';

EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'错误行号（MSSQL的行号非编辑器行号）', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
     @level1name = N'su_ProcExecErrorInfo', @level2type = N'COLUMN', @level2name = N'ErrorLine';

EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'可能错误的语句（跟踪前后）', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
     @level1name = N'su_ProcExecErrorInfo', @level2type = N'COLUMN', @level2name = N'SqlText';
GO