编写一个基本的SQL-Server存储过程

问题

/*
	创建一个存储过程输入参数为 DelayDate，
	需求是将Auth表中 indate 大于 DelayDate 的数据 LastEditUser字段修改为短名，
	
	且每修改一笔数据，
	需要将修改记录写入临时表中,且需要保证修改 Auth 表记录与插入临时表的记录的数据一致性，
	最终输出临时表中的记录
*/

• 编写存储过程

if object_id('dbo.hk5') is not null
	drop proc dbo.hk5
go
CREATE PROC dbo.hk5(   -- 传入多少更新多少
	@DelayDate datetime,
	@batchSize int
	)
AS

if object_id('#tmptb','P')is not null
	drop table #tmptb
CREATE TABLE #tmptb  -- 用于在存储过程中进行表替换
(
	TransactionNumber int ,
	EditUserName varchar(15),
	indate datetime,
	inlog nvarchar(128)
)
		
BEGIN
	WHILE(1=1)
	begin
		-- 创建变量
		declare @TransactionNumber int,@EditUserName varchar(15)='test',@indate datetime,@inlog nvarchar(128), @Count INT=0   																																																																															
		BEGIN
		
			-- 先创建游标 把要修改的数据查询出来
			declare data_cursor cursor local FORWARD_ONLY READ_ONLY for
			select
				TOP(@batchSize)
				TransactionNumber		
			FROM	
				Auth WITH(NOLOCK)
			WHERE
				indate > @DelayDate and 
				(LastEditUser is null or LastEditUser != @EditUserName )
		
			OPEN DATA_CURSOR  -- 打开游标 每次获取下一笔数据的时间
			FETCH NEXT FROM DATA_CURSOR INTO @TransactionNumber
			WHILE @@FETCH_STATUS = 0 -- 开始循环
			begin
				select @Count = @Count + 1
				-- 更新一笔 插入一笔
				begin try
					begin transaction  -- 回滚的话。。涉及修改的都可以加事务 ?
						-- 更新  
							update  TOP (1) Auth
								set LastEditUser = @EditUserName
							WHERE
								TransactionNumber = @TransactionNumber;			
						-- 插入一笔数据
							set @indate = getDate()
							set @inlog = N'测试'
							insert into #tmptb values(@TransactionNumber,@EditUserName,@indate,@inlog);
					commit transaction -- 成功提交
				end try
				begin catch
					 DECLARE @Error_Description NVARCHAR(4000)
					 SET @Error_Description =
							N'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))+  -- 系统内置函数：ERROR_NUMBER()
							N'Error Message : ' + ERROR_MESSAGE()+
							N'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))+ -- CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型
							N'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10))+
							N'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10));
					 DECLARE                   
							@ErrorMessage    NVARCHAR(4000)
							,@ErrorNumber     INT
							,@ErrorSeverity   INT
							,@ErrorState      INT
							,@ErrorLine       INT
							,@ErrorProcedure  NVARCHAR(200);
					 SELECT                   
							@ErrorNumber = ERROR_NUMBER()
							,@ErrorSeverity = ERROR_SEVERITY()
							,@ErrorState = ERROR_STATE()
							,@ErrorLine = ERROR_LINE()
							,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
							,@ErrorMessage = ERROR_MESSAGE();
					 RAISERROR(           -- 用于抛出一个异常或错误。这个错误可以被程序捕捉到。       
							N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message %s ', 
							@ErrorSeverity,               1, 
							@ErrorNumber,    -- parameter: original error number. 
							@ErrorSeverity,  -- parameter: original error severity. 
							@ErrorState,     -- parameter: original error state.  
							@ErrorProcedure, -- parameter: original error procedure name. 
							@ErrorLine,      -- parameter: original error line number.
							@ErrorMessage    -- parameter: original error message.
							); 
					rollback  transaction-- 报错回滚
				end catch
				FETCH NEXT FROM DATA_CURSOR INTO @TransactionNumber
			end
			CLOSE DATA_CURSOR   -- 关闭，释放游标
			DEALLOCATE DATA_CURSOR
		END
		if @Count < @batchsize
			break	
	END
select TransactionNumber, EditUserName, indate, inlog from #tmptb;	
END
if object_id('#tmptb','P')is not null
BEGIN
	drop table #tmptb
END

go