CODEWITHSUNDEEP
sqlsql-serverforeach
Gopalakrishnan Cassie
Gopalakrishnan Cassie

Reputation: 29

How to use for each in stored procedure?

In the below code i used single @partyID=750004 but now the scenario needs to use more @partyID. FYI like 750005,750007,750009,7500011...

How can i loop using "For each" 

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

Upvotes: 0

Views: 84

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

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:

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 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. 

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

Upvotes: 0

Hitesh
Hitesh

Reputation: 3498

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

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

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:

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

Upvotes: 0

Related Questions