SQL Server T-SQL stored procedure that takes a parameter


/ Published in: SQL
Save to your folder(s)

Loops through result set and performs a function row by row. Note, this example is contrived.


Copy this code and paste it in your HTML
  1. IF EXISTS (SELECT * FROM sysobjects WHERE name = N'sp_UpdatePortalAccess') DROP PROCEDURE sp_UpdatePortalAccess
  2.  
  3. GO
  4.  
  5. CREATE PROCEDURE sp_UpdatePortalAccess
  6. @caseID INT -- if there were more input params, just comma delimit on separate lines
  7. AS
  8. DECLARE @CaseMap_ID INT
  9.  
  10. DECLARE CaseMapCursor CURSOR FOR
  11. SELECT CaseMap_ID FROM CaseMap WHERE case_ID = @caseID
  12.  
  13. OPEN CaseMapCursor
  14.  
  15. FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID
  16.  
  17. WHILE @@Fetch_Status = 0
  18. BEGIN
  19. BEGIN TRANSACTION
  20. UPDATE CaseMap SET PortalAccess = 'Y', DateModified=GETDATE() WHERE CaseMap_ID = @CaseMap_ID
  21. COMMIT
  22. FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID
  23. END
  24.  
  25. CLOSE CaseMapCursor
  26. DEALLOCATE CaseMapCursor
  27. RETURN
  28.  
  29. --Example call
  30. --EXECUTE sp_UpdatePortalAccess 2;

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.