Advanced Search

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.

Expand | Embed | Plain Text

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.