Sql server存儲過程中常見游標循環用法 - tuyile006－IT工程師數位筆記本

文章出處

用游標，和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