原文:Sql server存储过程中常见游标循环用法
用游标,和WHILE可以遍历您的查询中的每一条记录并将要求的字段传给变量进行相应的处理
DECLARE @A1 VARCHAR(10), @A2 VARCHAR(10), @A3 INT DECLARE YOUCURNAME CURSOR FOR SELECT A1,A2,A3 FROM YOUTABLENAME OPEN YOUCURNAME fetch next from youcurname into @a1,@a2,@a3 while @@fetch_status<>-1 begin --您要执行的操作写在这里 fetch next from youcurname into @a1,@a2,@a3 end close youcurname deallocate youcurname
再加上异常捕捉和事务,完整过程如下:
Create PROCEDURE [dbo].[Usp_CreatePanicBuyingCode]( @OrderNumber VARCHAR(50)) AS DECLARE @A1 VARCHAR(10), @A2 VARCHAR(10), @A3 INT begin try begin tran DECLARE youcurname CURSOR FOR SELECT A1,A2,A3 FROM YOUTABLENAME OPEN youcurname fetch next from youcurname into @a1,@a2,@a3 while @@fetch_status<>-1 begin --您要执行的操作写在这里 fetch next from youcurname into @a1,@a2,@a3 end close youcurname deallocate youcurname COMMIT TRAN end try begin catch ROLLBACK end catch
时间: 2024-11-13 13:56:19