Posted By

housecor on 10/08/10


Tagged


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Tyster


SQL Server T-SQL stored procedure that takes a parameter


 / Published in: SQL
 

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

  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  

You need to login to post a comment.