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