/ Published in: SQL

Loops through result set and performs a function row by row. Note, this example is contrived.
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'sp_UpdatePortalAccess') DROP PROCEDURE sp_UpdatePortalAccess GO CREATE PROCEDURE sp_UpdatePortalAccess @caseID INT -- if there were more input params, just comma delimit on separate lines AS DECLARE @CaseMap_ID INT DECLARE CaseMapCursor CURSOR FOR SELECT CaseMap_ID FROM CaseMap WHERE case_ID = @caseID OPEN CaseMapCursor FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID WHILE @@Fetch_Status = 0 BEGIN BEGIN TRANSACTION UPDATE CaseMap SET PortalAccess = 'Y', DateModified=GETDATE() WHERE CaseMap_ID = @CaseMap_ID COMMIT FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID END CLOSE CaseMapCursor DEALLOCATE CaseMapCursor RETURN --Example call --EXECUTE sp_UpdatePortalAccess 2;
Comments
