Sql Server 使用while 循环数据

最新推荐文章于 2024-02-21 19:36:29 发布

月牙天羽

最新推荐文章于 2024-02-21 19:36:29 发布

阅读量298

点赞数

文章标签：数据库 sql

本文链接：https://blog.csdn.net/NARUTONEPIECE/article/details/133794111

版权


  
declare @workId int = 0;
declare @userId int = 123;
 
if @userId <=0
begin 
    Print 'User name error';
    return;
end

declare @table_BookId table (BookId int);

select @workId as workId;

insert into @table_BookId  select BookId from Books  

select BookId,Code,BookTitle,BookDate,CreateTime from Books 
where BookId in (select * from @table_BookId);

begin transaction --开启事务
    
	declare @BookId int = 0;
	declare @actionHistoryId int =0;

    while exists(select BookId from @table_BookId) --是否存在数据，存在则进入循环
	begin 
	    select top 1 @BookId = BookId from @table_BookId --获取首条数据Id

		Update BookParties set State=1,userId = @userId
		where BookId = @BookId  

		set @actionHistoryId =0;

		INSERT INTO [dbo].[Action] 
				   ([BookId]
				   ,[Action]
				   ,[ActionPartyId]
				   ,[ActionParty]
				   ,[ActionById]
				   ,[ActionBy]
				   ,[ActionTimestamp]
				   ,[ActionType])
			 VALUES
				   (@BookId
				   ,'Response'
				   ,@workId
				   ,@workgroupName
				   ,@userId
				   ,@responseUserName
				   ,GETDATE()
				   ,0)
	    select @actionHistoryId = @@identity;

		INSERT INTO [dbo].[ActionDetails]  ([UpdatedValue] ,[actionHistoryId] ,[FieldName])
			 VALUES ('ABC' ,@actionHistoryId, @workgroupName);
 

	    delete from @table_BookId where BookId = @BookId; --操作完后，删除该Id，避免一直循环
	end
commit transaction --提交事务

select 'Executed successfully' as 'Operation Tips'
Print 'complete the operation';