UPDATE Cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

UPDATE Cursor

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-20 : 09:46:13

Jason writes "----------Background of problem
I inherited a database for tracking equipment request and processing.

The stored procedure would need to scan the table and change any priority of the same value or higher by adding a one to them. In this way, any priority values of 2 would be changed to a 3 if we are adding a priority 2 value. for a specific Service.

The Maintable is called : er2001maintable
er2001maintable.EIL
er2001maintable.Priority
er2001maintable.RequestID --() primary Key
er2001maintable.EquipNomenclature....
... more data fields...

Table where Requesting Service info : eilview
eilview.EIL --() primary key
eilview.ReqService
eilview.Expr1

How to increment priority in a cursor for all request for specific requesting service?
(i.e ReqService='DIRECTOR' EIL=00D
ReqService='DIRECTOR' EIL=010
ReqService='DIRECTOR' EIL=012 )

Only ONE #1 priority is allowed per Service No matter how many different EIL's a Service has.

---------------------------------------
What I have so far using a cursor. It updates the priority + 1 for ALL SERVICES. I need to limit the update to a specific Service. (i.e. if ReqService = 'Director' update All A.Priority WHERE B.ReqService = 'Director')

------------- code with cursor ---------------------------

CREATE Procedure Crsr1_update AS
DECLARE @priority int, @Service nvarchar(50),@ReqSERVICE nvarchar(50)


DECLARE Crsr1 CURSOR FOR
SELECT A.priority,B.ReqService
FROM er2001maintable A,eilview B
WHERE A.priority > 2 AND A.EIL = B.EIL
FOR UPDATE OF A.Priority

OPEN Crsr1		 -- OPEN ABOVE SELECTION FOR UPDATE

Fetch NEXT FROM  Crsr1	-- 1st FETCH INTO @PRIORITY
INTO @priority, @Service

WHILE @@FETCH_STATUS = 0 --AND @Service = A Specific SERVICE
			 --WHILE FETCH statement was successful.
BEGIN	                  -- BEGIN WHILE

	PRINT @Service			-- display @Service value
	FETCH NEXT FROM Crsr1		-- move to next record
	INTO @priority,@Service

	UPDATE er2001maintable		-- UPDATE TABLE
	SET priority = priority + 1	-- increment priority + 1
	WHERE current of Crsr1		-- ***ON CURRENT VALUE!**	
	PRINT @priority	 		-- display @priority value
END 					-- END WHILE

CLOSE Crsr1				-- Close Cursor Crsr1
DEALLOCATE Crsr1		   -- Deallocate cursor Crsr1

Thanks in Advance for any thoughts on how to fix my code or rewrite needed.

platforms used:
SQL 2000 Server 2000 SP2
ACCESS 2000 Data Access Pages ( on Intranet)

// Jason"

ToddV
Posting Yak Master

218 Posts

Posted - 2002-03-20 : 13:19:36

Jason,
I can not see any reason why you would need a cursor to do this at all. A Simple update should get the job done.

Update A
SET Priority = Priority +1
FROM er2001maintable A
JOIN eilview B
ON A.EIL = B.EIL
WHERE ReqService = @ReqSERVICE

Perhaps I have missed something though. If so, please clarify requirements.

jturner1037
Starting Member

1 Post

Posted - 2002-03-20 : 14:29:04

Thanks Todd, for your quick post.
Here is what I have now.

CREATE PROC proc_SimpleUpdate
@ReqService nvarchar(50)
AS
Update A
SET Priority = Priority + 1
FROM er2001maintable A
JOIN eilview B
ON A.EIL = B.EIL
WHERE ReqService = @ReqSERVICE

GO

I want to call the stored proc
from a DATA ACCESS page and feed the proc the @ReqService from
selection on the DAP. or a Form in the equipment.ADP

quote:

Jason,
I can not see any reason why you would need a cursor to do this at all. A Simple update should get the job done.

Update A
SET Priority = Priority +1
FROM er2001maintable A
JOIN eilview B
ON A.EIL = B.EIL
WHERE ReqService = @ReqSERVICE

Perhaps I have missed something though. If so, please clarify requirements.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources