Sql Server在存储过程里面使用游标遍历一个表 USE [CapacityManagement] GO / Object: StoredProcedure [dbo].[USP_uploadResGpMaster] Script Date: 2018/10/24 10:09:03 / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO –创建一张表，用来接收C#程序传过来的DataTable对象 –注意，这张表的字段必须跟传过来的DataTable对象的属性一致，名字可以不同 create type GS_Master as table ( ResGp varchar(10), Plant varchar(12), Dept varchar(12), Descrip nvarchar(50), Step_mapping varchar(10), CreateDate datetime, CreateBy char(8), ChangeDate datetime, ChangeBy char(8) ) go CREATE PROCEDURE [dbo].[USP_uploadMaster] @Master GS_Master readonly,@OperationType nvarchar(3) AS BEGIN SET NOCOUNT ON; begin try begin transaction if(@OperationType =’A’) –声明一些local变量，用于接收查询表得到的数据，以便操作 declare @ResGp varchar(10), @Plant varchar(12), @Dept varchar(12), @Descrip nvarchar(50), @Step_mapping varchar(10), @CreateDate datetime, @CreateBy char(8), @ChangeDate datetime, @ChangeBy char(8), @master_id int begin –一、声明游标 declare master_cursor cursor for select * from @Master –二、打开游标 open master_cursor –三、取第一条数据 fetch next from master_cursor into @ResGp, @Plant, @Dept, @Descrip, @Step_mapping, @CreateDate, @CreateBy, @ChangeDate, @ChangeBy while @@FETCH_STATUS = 0 begin –四、操作数据,先操作[ResGp_Master]，后操作[Step_ResGp_Maping] –1、操作[ResGp_Master] SELECT @master_id=id FROM [CapacityManagement].[dbo].[ResGp_Master] where resgp = @ResGp and plant = @Plant and dept = @Dept update [CapacityManagement].[dbo].[ResGp_Master] set ChaDate = @ChangeDate, ChaBy = @ChangeBy, ResGpDesc = @Descrip where id = @master_id IF NOT EXISTS (SELECT id FROM [CapacityManagement].[dbo].[ResGp_Master] WHERE id = @master_id) INSERT INTO [CapacityManagement].[dbo].[ResGp_Master] (ResGp, Dept, CreDate, CreBy, ChaDate, ChaBy, ResGpDesc, plant) values(@ResGp, @Dept, @CreateDate, @CreateBy, @ChangeDate, @ChangeBy, @Descrip, @Plant) –2、操作[Step_ResGp_Maping] update [CapacityManagement].[dbo].[Step_ResGp_Maping] set ChaDate = @ChangeDate, ChaBy = @ChangeBy where Master_id = @master_id and step = @Step_mapping if not exists(select master_id from [CapacityManagement].[dbo].[Step_ResGp_Maping] where Master_id = @master_id and step = @Step_mapping) insert into [CapacityManagement].[dbo].[Step_ResGp_Maping](step, resgp, CreDate, CreBy, ChaDate, ChaBy, Master_id) values(@Step_mapping, @ResGp, @CreateDate, @CreateBy, @ChangeDate, @ChangeBy, @master_id) –五、取下一条数据 fetch next from master_cursor into @ResGp, @Plant, @Dept, @Descrip, @Step_mapping, @CreateDate, @CreateBy, @ChangeDate, @ChangeBy end –六、关闭游标 close master_cursor –七、释放游标 deallocate master_cursor end commit transaction end try begin catch select ERROR_MESSAGE() as errorMessage rollback transaction end catch END GO
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/70136.html
