Sql Server 使用while 循环数据

最新推荐文章于 2025-01-23 00:42:56 发布

原创最新推荐文章于 2025-01-23 00:42:56 发布 · 465 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql

博客涉及数据库与SQL相关内容，但具体信息缺失。数据库是数据存储管理的重要工具，SQL是用于操作数据库的标准语言，二者在信息技术领域应用广泛。


  
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';