Cursor alternative WHILE - 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 2005 Forums

Transact-SQL (2005)

Cursor alternative WHILE

Author

Topic

Tomji
Starting Member

19 Posts

Posted - 2010-07-12 : 10:42:12

I got a small job to do where a Cursor seems a bit overblown and also needs to many lines of code. I came up with the below:

WHILE EXISTS ( SELECT TOP 1 ApplicationID
FROM tb_Applications_Approvers AA
WHERE SequenceNumber > 0
)
BEGIN
<Do code wit just one ApplicationID>
<Now delete the row in original table or update SequenceNumber>
END

This works, but I would much prefer this approach:

WHILE EXISTS ( SELECT TOP 1 @AppId = ApplicationID
FROM tb_Applications_Approvers AA
WHERE SequenceNumber > 0
)

The difference is that I should be able to use @AppId directly in my BEGIN END codeblock. However it errors out with "Incorrect syntax near '='."
If I remove the WHILE EXIST (...) from the query it works and assigns a value to @AppId.

Anything I can do to make this work? Or someone knows an even simpler approach to the FOR EACH question.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-12 : 10:53:24

Think setbased.
What you want to do for one ID at a time that can maybe done in one step for all records.

Maybe you can show us what is coming up behind
<Do code wit just one ApplicationID>
<Now delete the row in original table or update SequenceNumber>

No, you're never too old to Yak'n'Roll if you're too young to die.

Tomji
Starting Member

19 Posts

Posted - 2010-07-12 : 11:32:29

the code updates and inserts data in other tables.
for my sanity I prefer to do it per ID, esp. since there wont be that many, I guess around 10 rows.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-12 : 12:15:24

quote:
the code updates and inserts data in other tables.

This is still possible in a set-based manner. If you post your code we can take a crack at it.

quote:
for my sanity I prefer to do it per ID, esp. since there wont be that many, I guess around 10 rows.

Your sanity will be tested if this has any chance of reaching 100, 1000 or more rows. Better to start going set-based when you don't need it than to struggle with it later when you do.

Tomji
Starting Member

19 Posts

Posted - 2010-07-12 : 14:04:06

Ok, I finished my code for using a WHILE loop.


DECLARE @TargetApproverID INT
SET @TargetApproverID = 12

DECLARE @SequenceTable TABLE (
		ApplicationID INT
		,ApproverID INT
		,SequenceID INT
		)

WHILE EXISTS (
		SELECT TOP 1 ApplicationID
		FROM tb_Applications_Approvers AA
		WHERE ApproverID = @TargetApproverID
		AND SequenceNumber > 0
		)
BEGIN
		INSERT INTO @SequenceTable
		SELECT ApplicationID, ApproverID, SequenceNumber
		FROM tb_Applications_Approvers AA
		WHERE ApplicationID = (SELECT TOP 1 ApplicationID FROM tb_Applications_Approvers WHERE ApproverID = @TargetApproverID AND SequenceNumber > 0)
		AND ApproverID <> @TargetApproverID
		
		SELECT * FROM @SequenceTable

		DELETE FROM tb_Applications_Approvers
		WHERE ApplicationID = (SELECT TOP 1 ApplicationID FROM @SequenceTable)

		 /* Recreate Chain of Approvers */
	     INSERT INTO [tb_Applications_Approvers] (ApplicationID, ApproverID, SequenceNumber)
		 SELECT ApplicationID, ApproverID, ROW_NUMBER() OVER (ORDER BY ApplicationID) AS SequenceNumber FROM @SequenceTable

		DELETE FROM @SequenceTable /* Clean our worktable */
		PRINT 'Reassigned Chain Sequence for Application'
END

		/* We Made sure the apps the approver is assigned to has at least another approver - We also reassigned Chain Sequence Numbers */
		DELETE FROM tb_ApproverGroup_SecurityRole  WHERE ApproverID = @TargetApproverID
		DELETE FROM tb_ApproverGroup_Approver WHERE ApproverID = @TargetApproverID
		DELETE FROM tb_Applications_Approvers WHERE ApproverID = @TargetApproverID
		DELETE FROM tb_Approver WHERE ApproverID = @TargetApproverID
		PRINT 'Approver Deleted'
		GOTO RealEnd
		
NoneDelete:
PRINT 'Not Deleted Anything'

RealEnd:

Tomji
Starting Member

19 Posts

Posted - 2010-07-12 : 14:05:59

The code I posted works.
>> SELECT * FROM @SequenceTable
This was just there for troubleshooting.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-12 : 14:50:51

I believe this will work but please test it thoroughly against your original code:

DECLARE @TargetApproverID INT
SET @TargetApproverID = 12

DECLARE @apps TABLE (ApplicationID int)

DELETE FROM tb_Applications_Approvers 
OUTPUT deleted.ApplicationID INTO @apps
WHERE ApproverID = @TargetApproverID AND SequenceNumber > 0

;WITH CTE(ApplicationID,ApproverID,SequenceID,NewSeq) AS
	(SELECT ApplicationID,ApproverID,SequenceNumber, ROW_NUMBER() OVER (PARTITION BY ApplicationID ORDER BY ApplicationID)
	FROM tb_Applications_Approvers
	WHERE ApplicationID IN(SELECT ApplicationID FROM @apps))
UPDATE CTE SET SequenceID=NewSeq

Tomji
Starting Member

19 Posts

Posted - 2010-07-12 : 15:02:13

It does indeed work...
I will use it, however first I need to better understand it (so that I can use this approach myself in the future)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources