如何在存储过程中使用每个？-Howtouseforeachinstoredprocedure?

1  

DECLARE cur CURSOR FOR 

SELECT partyid from table1    // @partyID like 750005,750007,750009,7500011

FETCH NEXT FROM cur INTO @partyID

WHILE (@@FETCH_STATUS=0)
BEGIN

    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

FETCH NEXT FROM cur INTO @partyID

END

CLOSE cur
DEALLOCATE cur

0  

You seem to want to use a CURSOR. Though most of the times it's best to use a set based solution, there are some times where a CURSOR is the best solution. Without knowing more about your real problem, we can't help you more than that:

你似乎想要使用CURSOR。虽然大多数情况下最好使用基于集合的解决方案,但有时候CURSOR是最佳解决方案。在不了解您的真实问题的情况下,我们无法帮助您:

DECLARE @PractitionerId int

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT DISTINCT PractitionerId 
FROM Practitioner

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @PractitionerId
WHILE @@FETCH_STATUS = 0
BEGIN 
    --Do something with Id here
    PRINT @PractitionerId
    FETCH NEXT FROM MY_CURSOR INTO @PractitionerId
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

0  

The best option is to get rid of the cursors and use a set based approach instead.

最好的选择是摆脱游标并使用基于集合的方法。

First, declare a table variable to store all IDs:

首先,声明一个表变量来存储所有ID:

DECLARE @partyID TABLE (partyID int)

INSERT INTO @partyID VALUES 
(750005),(750007),(750009),(7500011)

Then perform INSERT INTO SELECT and UPDATE operations using this table variable:

然后使用此表变量执行INSERT INTO SELECT和UPDATE操作:

INSERT INTO QAdmin.PartyLicenseInfo (PartyId, [State], LicenseNumber, 
            LicenseExpirationDate, CreatedBy, CreatedDate, ModifiedBy,
            ModifiedDate) 
SELECT partyID, 'SC', 'SC1234', getdate()+360, 0, GETDATE(), null, null 
FROM @partyID AS p1
WHERE NOT EXISTS (SELECT 1
                  FROM [QAdmin].[PartyLicenseInfo] AS p2
                  WHERE p2.PartyId = p1.partyID AND [State]='SC')

The above will insert new records into PartyLicenseInfo only if your original NOT EXISTS clause returns TRUE.

仅当您的原始NOT EXISTS子句返回TRUE时,上述内容才会将新记录插入PartyLicenseInfo。

UPDATE p1
SET 
   LicenseExpiratiOnDate= getdate()+360, ModifiedBy = 0, 
   ModifiedDate = GETDATE() 
FROM [QAdmin].[PartyLicenseInfo] AS p1
INNER JOIN @partyID AS p2 ON p1.PartyID = p2.partyID
WHERE [State]='SC'

The above will update records of PartyLicenseInfo having PartyID values contained in @partyID and [State]='SC'.

以上内容将更新PartyLicenseInfo的记录,其中PartyID值包含在@partyID和[State] ='SC'中。