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