Revision: 33400
Updated Code
at October 8, 2010 12:13 by housecor
Updated Code
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;
Revision: 33399
Updated Code
at October 8, 2010 12:09 by housecor
Updated Code
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' WHERE CaseMap_ID = @CaseMap_ID COMMIT FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID END CLOSE CaseMapCursor DEALLOCATE CaseMapCursor RETURN --TO EXECUTE: --EXECUTE sp_UpdatePortalAccess 2;
Revision: 33398
Updated Code
at October 8, 2010 12:02 by housecor
Updated Code
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'sp_UpdatePortalAccess') DROP PROCEDURE sp_UpdatePortalAccess GO CREATE PROCEDURE sp_UpdatePortalAccess @caseID int --if more input params, just comma delimit on separate lines AS DECLARE @CaseMap_ID int DECLARE CaseMapCursor CURSOR FOR SELECT CaseMap_ID FROM CustomerTable WHERE case_ID = @caseID OPEN CaseMapCursor FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID WHILE @@Fetch_Status = 0 BEGIN UPDATE CaseMap SET PortalAccess='Y' WHERE CaseMap_ID = @CaseMap_ID FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID END CLOSE CaseMapCursor DEALLOCATE CaseMapCursor RETURN
Revision: 33397
Updated Code
at October 8, 2010 12:01 by housecor
Updated Code
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'sp_UpdatePortalAccess') DROP PROCEDURE sp_UpdatePortalAccess GO CREATE PROCEDURE sp_UpdatePortalAccess @caseID int AS DECLARE @CaseMap_ID int DECLARE CaseMapCursor CURSOR FOR SELECT CaseMap_ID FROM CustomerTable WHERE case_ID = @caseID OPEN CaseMapCursor FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID WHILE @@Fetch_Status = 0 BEGIN UPDATE CaseMap SET PortalAccess='Y' WHERE CaseMap_ID = @CaseMap_ID FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID END CLOSE CaseMapCursor DEALLOCATE CaseMapCursor RETURN
Revision: 33396
Updated Code
at October 8, 2010 12:00 by housecor
Updated Code
if exists (select * from sysobjects where name = N'sp_UpdatePortalAccess') drop procedure sp_UpdatePortalAccess go CREATE PROCEDURE sp_UpdatePortalAccess @caseID int AS DECLARE @CaseMap_ID int DECLARE CaseMapCursor CURSOR FOR SELECT CaseMap_ID FROM CustomerTable WHERE case_ID = @caseID OPEN CaseMapCursor FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID WHILE @@Fetch_Status = 0 BEGIN UPDATE CaseMap SET PortalAccess='Y' WHERE CaseMap_ID = @CaseMap_ID FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID END CLOSE CaseMapCursor DEALLOCATE CaseMapCursor RETURN
Revision: 33395
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at October 8, 2010 11:57 by housecor
Initial Code
CREATE PROCEDURE sp_UpdatePortalAccess @caseID int AS DECLARE @CaseMap_ID int DECLARE CaseMapCursor CURSOR FOR SELECT CaseMap_ID FROM CustomerTable WHERE case_ID = @caseID OPEN CaseMapCursor FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID WHILE @@Fetch_Status = 0 BEGIN UPDATE CaseMap SET PortalAccess='Y' WHERE CaseMap_ID = @CaseMap_ID FETCH NEXT FROM CaseMapCursor INTO @CaseMap_ID END CLOSE CaseMapCursor DEALLOCATE CaseMapCursor RETURN
Initial URL
Initial Description
Loops through result set and performs a function row by row. Note, this example is contrived.
Initial Title
SQL Server T-SQL stored procedure that takes a parameter
Initial Tags
Initial Language
SQL