我如何使用“For Each”循环循环存储过程

本文介绍了我如何使用“For Each”循环循环存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 在下面的代码我用单个@ partyID = 750004但现在的情况下，需要使用更@partyID ... FYI像750005,750007,750009,7500011,7500021,75000251。 BEGIN TRY BEGIN TRAN 声明 @ partyID bigint = 750004 IF NOT EXISTS （ SELECT * FROM [QAdmin]。[PartyLicenseInfo] WHERE partyId = @ partyID AND [State] = ' SC'） BEGIN INSERT INTO QAdmin.PartyLicenseInfo（PartyId，[State] ]，LicenseNumber，LicenseExpirationDate，CreatedBy，CreatedDate，ModifiedBy，ModifiedDate） VALUES （ @ partyID ，' SC'，' SC1234'，getdate（）+ 360， 0 ，GETDATE（）， null ， null ） END ELSE BEGIN UPDATE [ QAdmin]。[PartyLicenseInfo] SET LicenseExpira tionDate = getdate（）+ 360，ModifiedBy = 0 ，ModifiedDate = GETDATE（） WHERE PartyId = @ partyID AND [State] = ' SC' END COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN END CATCH 解决方案 尝试在存储过程中使用for循环 [ ^ ] 没有像。如果你想迭代你的数字列表，你可以使用 WHILE 循环。 首先，你需要迭代你的csv DECLARE @ partyIDs VARCHAR （MAX）= ' 750005,750007,750009， 7500011,7500021,75000251' 查看下面的链接，了解 http://data.stackexchange.com/stackoverflow/query/66016/walk-the-string [ ^ ] 然后，您需要将整个代码放在循环中（在上面的链接中给出），如： 声明 @ S varchar （ 20 ） set @ S = @ partyIDs len（ @ S ）> 0 开始 - 声明@partyID VARCHAR（20）= left（@S，charindex（'，'，@ S +'，'） - 1） - 您的代码 BEGIN 尝试 BEGIN TRAN - 声明@partyID bigint = 750004 声明 @ partyID bigint = CAST（ left （ @ S ，charindex（' ，'，@ S + ' ，'） - 1） AS bigint ） IF NOT EXISTS （ SELECT * FROM [QAdmin]。[PartyLicenseInfo] WHERE PartyId = @ partyID AND [State] = ' SC'） BEGIN INSERT INTO QAdmin.PartyLicenseInfo（PartyId，[State]，LicenseNumber，LicenseExpirationDate，CreatedBy，CreatedDate，ModifiedBy，ModifiedDate） VALUES （ @ partyID ，' SC'，' SC1234'，getdate（）+ 360， 0 ，GETDATE（）， null ， null ） END ELSE BEGIN 更新 [QAdmin]。[PartyLicenseInfo] SET LicenseExpirationDate = getdate（）+ 360，ModifiedBy = 0 ，ModifiedDate = GETDATE（） WHERE PartyId = @ partyID AND [State] = ' SC' END COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN 结束 CATCH 设置 @ S = stuff （ @ S ， 1 ，charindex（'' ，'，@ S + ' ，'），' '） end 您也可以使用函数方式。不确定你所使用的版本或任务需要的可扩展性......我会选择Abhipal输入的解决方案。 - ========================== ======================== - 创建功能dba_fn_SplitList - =========== ======================================= 创建功能[dbo]。[ dba_fn_SplitList]（@ param NVARCHAR（MAX），@ delimiter CHAR（1）） RETURNS @t TABLE（val VARCHAR（150），seq INT） AS / ***** ************************************************** ************************* \ *描述：将分隔列表拆分为包含每个值和序列的表。 * Seq是唯一的，因此可以在 *函数之外删除空格和重复项。 \ ******************************************** ************************************ / BEGIN SET @param + = @delimiter ; WITH A $ （ SELECT CAST（1 AS BIGINT）F，CHARINDEX（@ delimiter，@ param）T，1 Seq UNION所有 SELECT T + 1，CHARINDEX（@ delimiter，@ param，t + 1），Seq + 1 来自A WHERE CHARINDEX（@ delimiter，@ param，t + 1）> ; 0 ） INSERT @t SELECT SUBSTRING（@ param，f，t - f），seq 来自A 选项（MAXRECURSION 0）返回结束 GO - ============================ ================================================ - 获取PartyIdList - ===================================== ======================================= DECLARE @PartyIDList VARCHAR（MAX） SET @PartyIDList = '75000A5,750007,1,3434,4546,676,750009,7500011,7500021,75000251，，75000251,750004' BEGIN TRY IF OBJECT_ID （ tempdb.dbo。＃Party'）IS NOT NULL DROP TABLE #Party CREATE TABLE #Party（RID INT IDENTITY（1,1）NOT NULL PRIMARY KEY，PartyId BIGINT NOT NULL） DECLARE @PartyID BIGINT = 0 DECLARE @min INT，@ max INT - ================== ================================================== ======== - 获取每个有效的PartyID - ========================== ================================================== INSERT #Party（PartyId） SELECT DISTINCT Val FROM dbo.dba_fn_SplitList（@PartyIDList，'，'） WHERE Val<> ''AND Val NOT LIKE'％[^ 0-9]％' SELECT @min = MIN（RID），@ max = MAX（RID）FROM #Party WHILE（@min< = @max） BEGIN SELECT TOP 1 @partyID = PartyID FROM #Party P WHERE P.RID = @min - ======================================== ==================================== - 更新状态$ b $的现有PartyIds b - =============================================== ============================= UPDATE PL SET LicenseExpirationDate = DATEADD（DAY，DATEDIFF（DAY，DAY） 0，GETDATE（））+ 360,0）， ModifiedBy = 0， ModifiedDate = GETDATE（） FROM QAdmin.PartyLicenseInfo PL WHERE PL.PartyID = @PartyID AND PL.State ='SC' - ============================= $============================================== - 插入不存在为州 - ========================================= =================================== INSERT QAdmin.PartyLicenseInfo（PartyId，[State]， LicenseNumber，LicenseExpirationDate，CreatedBy，CreatedDate，ModifiedBy，ModifiedDate） SELECT @PartyId， State ='SC'， LicenseNumber ='SC1234'， LicenseExpirationDate = DATEADD（DAY， DATEDIFF（DAY，0，GETDATE（））+ 360,0）， CreatedBy = 0， CreatedDate = GETDATE（）， ModifiedBy = NULL， ModifiedDate = NULL WHERE NOT EXISTS（从QAdmin.PartyLicenseInfo DST WITH（NOLOCK）中选择1 WHERE DST.PartyID = @PartyId AND DST.State ='SC'） SET @min = @min + 1 END END TRY BEGIN CATCH SELECT ERROR_NUMBER （）AS ErrorNumber， ERROR_MESSAGE（）AS ErrorMessage END CATCH GO - =============== =================================== - 结束!! - ============================================ == in the below code i used single @partyID=750004 but now the scenario needs to use more @partyID... FYI like 750005,750007,750009,7500011,7500021,75000251.BEGIN TRY BEGIN TRANDeclare @partyID bigint = 750004IF NOT EXISTS (SELECT * FROM [QAdmin].[PartyLicenseInfo] WHERE PartyId = @partyID AND [State]='SC') BEGIN INSERT INTO QAdmin.PartyLicenseInfo (PartyId, [State], LicenseNumber, LicenseExpirationDate, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate) VALUES (@partyID, 'SC', 'SC1234', getdate()+360, 0, GETDATE(), null, null) ENDELSE BEGIN UPDATE [QAdmin].[PartyLicenseInfo] SET LicenseExpirationDate = getdate()+360, ModifiedBy = 0, ModifiedDate = GETDATE() WHERE PartyId = @partyID AND [State]='SC' ENDCOMMIT TRAN END TRYBEGIN CATCH ROLLBACK TRANEND CATCH 解决方案 Try using for loop in stored procedure[^]There is nothing like foreach in SQL. If you want to iterate your list of numbers you can use a WHILE loop.First, you need to iterate your csvDECLARE @partyIDs VARCHAR(MAX)= '750005,750007,750009,7500011,7500021,75000251'check the link below to see howhttp://data.stackexchange.com/stackoverflow/query/66016/walk-the-string[^]Then, you need to fit your entire code inside the loop (given in above link) like:declare @S varchar(20)set @S = @partyIDswhile len(@S) > 0begin--declare @partyID VARCHAR(20)= left(@S, charindex(',', @S+',')-1)--your codeBEGIN TRYBEGIN TRAN--Declare @partyID bigint = 750004Declare @partyID bigint= CAST(left(@S, charindex(',', @S+',')-1) AS bigint)IF NOT EXISTS (SELECT * FROM [QAdmin].[PartyLicenseInfo] WHERE PartyId = @partyID AND [State]='SC')BEGININSERT INTO QAdmin.PartyLicenseInfo (PartyId, [State], LicenseNumber, LicenseExpirationDate, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)VALUES (@partyID, 'SC', 'SC1234', getdate()+360, 0, GETDATE(), null, null)ENDELSEBEGINUPDATE [QAdmin].[PartyLicenseInfo] SET LicenseExpirationDate = getdate()+360, ModifiedBy = 0, ModifiedDate = GETDATE()WHERE PartyId = @partyID AND [State]='SC'ENDCOMMIT TRANEND TRYBEGIN CATCHROLLBACK TRANEND CATCH set @S = stuff(@S, 1, charindex(',', @S+','), '')endYou can go the function way as well. Not sure what version you are on or how scalable the task needs to be.... I'd go with the solution entered by Abhipal.-- ==================================================-- CREATE FUNCTION dba_fn_SplitList-- ==================================================CREATE FUNCTION [dbo].[dba_fn_SplitList] (@param NVARCHAR(MAX), @delimiter CHAR(1))RETURNS @t TABLE (val VARCHAR(150), seq INT)AS/********************************************************************************\* Description: Split a delimited list into a table with each value and the sequence.* Seq is unique so blanks and duplicates can be removed outside the* function.\********************************************************************************/BEGIN SET @param += @delimiter ;WITH A AS ( SELECT CAST(1 AS BIGINT) F, CHARINDEX(@delimiter, @param) T, 1 Seq UNION ALL SELECT T + 1, CHARINDEX(@delimiter, @param, t + 1), Seq + 1 FROM A WHERE CHARINDEX(@delimiter, @param, t + 1) > 0 ) INSERT @t SELECT SUBSTRING(@param, f, t - f), seq FROM A OPTION (MAXRECURSION 0) RETURNENDGO-- ============================================================================-- Get the PartyIdList-- ============================================================================DECLARE @PartyIDList VARCHAR(MAX)SET @PartyIDList = '75000A5,750007,1,3434,4546,676,750009,7500011,7500021,75000251,,75000251,750004'BEGIN TRY IF OBJECT_ID('tempdb.dbo.#Party') IS NOT NULL DROP TABLE #Party CREATE TABLE #Party (RID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, PartyId BIGINT NOT NULL) DECLARE @PartyID BIGINT = 0 DECLARE @min INT, @max INT -- ============================================================================ -- Get each valid PartyID -- ============================================================================ INSERT #Party (PartyId) SELECT DISTINCT Val FROM dbo.dba_fn_SplitList(@PartyIDList,',') WHERE Val <> '' AND Val NOT LIKE '%[^0-9]%' SELECT @min = MIN(RID), @max = MAX(RID) FROM #Party WHILE (@min <= @max) BEGIN SELECT TOP 1 @partyID = PartyID FROM #Party P WHERE P.RID = @min -- ============================================================================ -- Update Existing PartyIds for State -- ============================================================================ UPDATE PL SET LicenseExpirationDate = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())+360,0), ModifiedBy = 0, ModifiedDate = GETDATE() FROM QAdmin.PartyLicenseInfo PL WHERE PL.PartyID = @PartyID AND PL.State = 'SC' -- ============================================================================ -- Insert Non-Existing for State -- ============================================================================ INSERT QAdmin.PartyLicenseInfo (PartyId, [State], LicenseNumber, LicenseExpirationDate, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate) SELECT @PartyId, State='SC', LicenseNumber = 'SC1234', LicenseExpirationDate = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())+360,0), CreatedBy = 0, CreatedDate = GETDATE(), ModifiedBy = NULL, ModifiedDate = NULL WHERE NOT EXISTS (SELECT 1 FROM QAdmin.PartyLicenseInfo DST WITH (NOLOCK) WHERE DST.PartyID = @PartyId AND DST.State = 'SC') SET @min = @min + 1 ENDEND TRYBEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessageEND CATCHGO-- ==================================================-- END!!-- ================================================== 这篇关于我如何使用“For Each”循环循环存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！