Return to Snippet

Revision: 33400
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
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
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
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
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
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