【SQL】存储过程模板

IF (OBJECT_ID('rp_procedure','P') IS NULL)
BEGIN
    EXEC ('CREATE PROCEDURE rp_procedure AS BEGIN SELECT 1; END');
END
GO
--遵循SQL-92标准,对空值(NULL)的等于 (=) 或不等于 (<>) 比较取值都为 FALSE
SET ANSI_NULLS ON
GO
--标识符可以由双引号分隔，而文字必须由单引号分隔
SET QUOTED_IDENTIFIER ON
GO
/*
存储过程的基本信息
=========================================================================================
	 Author:	林思源
Create date:	2019-10-22
Description:	存储过程

Modify  [1]: 
=========================================================================================
*/
ALTER PROCEDURE rp_procedure
(
	@i_rev VARCHAR(200),		--扫描内容
	@o_res VARCHAR(1000) OUTPUT --输出参数
)
AS
BEGIN
	DECLARE @tranCounter INT;		--记录事务指针
	SET @tranCounter = @@TRANCOUNT;
	--部分错误不会产生警告信息
	SET ANSI_WARNINGS OFF;
	--阻止在结果集中返回显示受T-SQL语句或则usp影响的行计数信息
	SET NOCOUNT ON;
	
	--有外层事务时则保存事务节点
	IF (@tranCounter > 0)
	BEGIN
		SAVE TRAN tran_procedure;
	END 
	ELSE
	BEGIN
		BEGIN TRAN tran_procedure;
	END 
	BEGIN TRY
        --执行内容

		--无外层事务时才提交
		IF (@tranCounter = 0)
		BEGIN
			COMMIT TRAN tran_procedure;
		END 
		RETURN;
		
		Label_Error:
		BEGIN
			ROLLBACK TRANSACTION tran_procedure;
			RETURN;
		END

	END TRY
	BEGIN CATCH
		PRINT 'CATCH ERROR FROM ' + ERROR_PROCEDURE() + ' IN ' + OBJECT_NAME(@@PROCID);
		DECLARE @ErrorMessage NVARCHAR(4000);
		SELECT @ErrorMessage = ERROR_MESSAGE();
		SET @o_res = @ErrorMessage;
		--事务活动
		IF XACT_STATE() = 1
		BEGIN
			ROLLBACK TRAN tran_procedure;
		END 
		--事务无法提交
		ELSE IF XACT_STATE() = -1
		BEGIN
			--非嵌套事务则回滚
			IF (@tranCounter = 0)
			BEGIN
				ROLLBACK TRAN;
			END 
			--外层还有事务则继续向上抛出异常
			ELSE
			BEGIN
				RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
			END 
		END 
	END CATCH	
END
GO