alter table P_RollBackConfigTab add UserMsg varchar(200)
go

/****** Object:  StoredProcedure [dbo].[P_VerfyRollBackConfig]    Script Date: 01/26/2015 22:26:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		gyc
-- Create date: 2014-12-22
-- Description:	验证当前单据是否可以回退
-- =============================================
/*
  DECLARE @msg VARCHAR(3000)
  EXEC [P_VerfyRollBackConfig] 'SLY14120001','12034',@msg OUTPUT
  SELECT @msg
*/
ALTER PROCEDURE [dbo].[P_VerfyRollBackConfig]
	@BilldocumentId VARCHAR(40),
	@BillTypeCode VARCHAR(20),
	@Msg VARCHAR(3000) OUTPUT
AS
BEGIN
	-- 验证参数
	IF ISNULL(@BilldocumentId,'')='' OR ISNULL(@BillTypeCode,'')=''
	BEGIN
		SET @Msg='参数传递错误.'
		GOTO ERROR
	END
	BEGIN TRY
		IF EXISTS(SELECT 1 FROM P_RollBackConfigTab WHERE BillCode=@BillTypeCode)
		BEGIN
			DECLARE @Sql NVARCHAR(MAX),@BillCode VARCHAR(10),@BillDesc VARCHAR(50),@BillTableName VARCHAR(50),
				@BillFieldKey VARCHAR(50),@NextBillCode VARCHAR(10),@NextBillDesc VARCHAR(50),
				@NextBillFieldKey VARCHAR(50),@NextBillTableName VARCHAR(50),@Result INT,
				@UserMessage varchar(200)
			   
			DECLARE MyCursor CURSOR FOR
			SELECT BillCode,BillDesc,BillFieldKey,BillTableName,NextBillCode,
				NextBillDesc,NextBillFieldKey,NextBillTableName,userMsg
			FROM P_RollBackConfigTab
			WHERE BillCode=@BillTypeCode
			
			OPEN MyCursor
			FETCH NEXT FROM MyCursor INTO @BillCode,@BillDesc,@BillFieldKey,@BillTableName,
				@NextBillCode,@NextBillDesc,@NextBillFieldKey,@NextBillTableName,@UserMessage
			WHILE @@FETCH_STATUS=0
			BEGIN				
				-- 验证是否可以回退
				SET @Sql = 'SELECT @Result=COUNT(1) FROM '+@BillTableName+' a'+
					' INNER JOIN '+@NextBillTableName+' b ON a.'+@BillFieldKey+'=b.'+@NextBillFieldKey+''+
					' WHERE a.Billdocument_Id='+QUOTENAME(@BilldocumentId,'''')
				EXEC SP_EXECUTESQL @Sql,N'@Result INT OUTPUT',@Result OUTPUT
				
				IF ISNULL(@Result,0) > 0
				BEGIN
					if ISNULL(@UserMessage,'')=''
						SET @Msg= @NextBillDesc+'单据中已经引用'+@BillDesc+'单据,无法回退.'
					else
						set @msg=@UserMessage
					GOTO ERROR
				END
				
				FETCH NEXT FROM MyCursor INTO @BillCode,@BillDesc,@BillFieldKey,@BillTableName,
					@NextBillCode,@NextBillDesc,@NextBillFieldKey,@NextBillTableName
			END
			
			CLOSE MyCursor -- 关闭游标
			DEALLOCATE MyCursor -- 释放游标
		END
	END TRY
	BEGIN CATCH
		SET @msg=LEFT(ERROR_MESSAGE(),3000)
		GOTO ERROR
	END CATCH
	
	RETURN 1

	ERROR:
		IF EXISTS(SELECT 1 FROM MASTER.dbo.SYSCURSORS WHERE cursor_name='MyCursor')
		BEGIN
			CLOSE MyCursor -- 关闭游标
			DEALLOCATE MyCursor -- 释放游标
		END
		RETURN -1
END