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