sql存储过程

存储过程格式游标带事务

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_XXX]
	@Args			NVARCHAR(MAX) = '',				--输入参数			Json串
	@ResultCode		INT = 0				OUTPUT,		--返回结果Code		0-正常 1-异常
	@ResultMsg		NVARCHAR(MAX) = ''	OUTPUT		--返回结果内容
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON

	DECLARE 
			@Remark                NVARCHAR(MAX)=''
	--创建临时表
	CREATE TABLE #T_Args
	(
		 Remark NVARCHAR(MAX)
	)

	EXEC sp_JsonToTable @Args
	
	--获取相关数据
	BEGIN 
		--临时表中提取数据
		SELECT @Remark = ISNULL(Remark, '')
		FROM #T_Args
	END
	BEGIN TRY
	  --开启事务
	  BEGIN TRAN
	   --验证
		BEGIN
	 
		DECLARE Sheet_Cursor CURSOR FOR
		SELECT Remark
		FROM #T_Args
		OPEN Sheet_Cursor; --打开游标 
		FETCH NEXT FROM Sheet_Cursor INTO @Remark
		WHILE @@FETCH_STATUS = 0     
		BEGIN
			
			 

		    FETCH NEXT FROM Sheet_Cursor INTO  @Remark
		END
		CLOSE Sheet_Cursor; --关闭游标 
		DEALLOCATE Sheet_Cursor; --释放游标 
		 
	 
		--事务提交
		COMMIT TRAN
		END
	END TRY 
	BEGIN CATCH
	      IF @@TRANCOUNT>0
		  BEGIN
			ROLLBACK TRAN
		  END

		  SET @ResultCode=1
		  SET @ResultMsg=ERROR_MESSAGE()

	END CATCH
	
	SET XACT_ABORT OFF
	SET NOCOUNT OFF
END