我如何使用“For Each”循环循环存储过程 [英] How Can I Loop Using "For Each" Loop In Stored Procedure

问题描述

在下面的代码我用单个@ partyID = 750004但现在的情况下，需要使用更@partyID ... FYI像750005,750007,750009,7500011,7500021,75000251。

  BEGIN  TRY 
  BEGIN  <跨度类= 代码关键字> TRAN  
 <跨度类= 代码关键字>声明 <跨度类= 代码sdkkeyword> @ partyID   bigint  =  750004  
  IF   NOT   EXISTS （ SELECT  *  FROM  [QAdmin]。[PartyLicenseInfo] <跨度类= 代码关键字> WHERE  partyId = <跨度类= 代码sdkkeyword> @ 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  
 
 
 < span class =code-keyword> 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' 
 < span class =code-keyword> 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 TRAN
Declare @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 LicenseExpirationDate = getdate()+360, ModifiedBy = 0, ModifiedDate = GETDATE() 
                           WHERE PartyId = @partyID AND [State]='SC'
                     END


COMMIT TRAN
       END TRY
BEGIN CATCH
       ROLLBACK TRAN
END 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 csv

DECLARE @partyIDs VARCHAR(MAX)= '750005,750007,750009,7500011,7500021,75000251'

check the link below to see how
http://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 = @partyIDs

while len(@S) > 0
begin
--declare @partyID VARCHAR(20)= left(@S, charindex(',', @S+',')-1)
--your code
BEGIN TRY
BEGIN TRAN
--Declare @partyID bigint = 750004
Declare @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
UPDATE [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
END CATCH 

  set @S = stuff(@S, 1, charindex(',', @S+','), '')
end

You 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)
    RETURN
END
GO

-- ============================================================================
-- 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
    END

END TRY
BEGIN CATCH
    SELECT  ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage
END CATCH
GO

--  ==================================================
--  END!!
--  ==================================================

这篇关于我如何使用“For Each”循环循环存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！