Alternative to Cursor use - 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)

Alternative to Cursor use

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2006-12-21 : 07:09:18

I am having a table that has a column called sequence. Structure of table is
Id int
CourseId int
Name varchar
Sequence int
and sample data is
Id CourseId Name Sequence
1 92 Chap1 1
2 92 Chap2 2
3 92 chap3 3

Now, when i delete second record i.e. Id=2, I want that sequence of Id=3 should become 2. For this I am using cursor.

CREATE TRIGGER [Et_TriggerReOrder] ON [dbo].[ET_CourseChapter]
FOR DELETE
AS
BEGIN
DECLARE @id as integer
DECLARE @Sequence as int
DECLARE @Count as int
DECLARE REorder_cursor CURSOR FOR
SELECT [id], [Sequence] FROM ET_CourseChapter WHERE courseid = (select courseid from deleted) order by [Sequence]

OPEN REorder_cursor
FETCH NEXT FROM REorder_cursor INTO @id, @Sequence

set @Count = 1
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE ET_CourseChapter SET [Sequence] = @Count WHERE ID = @id
--print cast(@id as varchar) + ' ' + cast(@Sequence as varchar)
set @Count = @Count + 1
FETCH NEXT FROM REorder_cursor INTO @id, @Sequence

END

CLOSE REorder_cursor
DEALLOCATE REorder_cursor
END

Can any one provide me with alternative solution where cursor is not used for such operation.
Regards

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-21 : 07:14:05

why do you want to mess with the id's?
You should just leave them as they are - you might have data in other tables that reference these id's.

My bad - you want to change the sequence not the id

Duane.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2006-12-21 : 07:16:52

I want the sequence to be reset on delete of any item of that group(in this case it is CourseId) as this is used in ordering in which these are displayed.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-21 : 07:17:03

Do the sequence no's run from 1 through to n?

or can they be reset somewhere along the line?

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-21 : 07:23:24

1 You dont need to worry about sequence. When you display data in front end application, do numbering there
2 If it is just for display I dont think you need seperate column
3 If you want to do this in sql, then try

Delete from table where Id=2
Update table set sequence=sequence-1 where Id>2

Madhivanan

Failing to plan is Planning to fail

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2006-12-21 : 07:27:09

yeah, sequence run from 1 to N and do change but with in limits.To make my self clear, in the data given, there are 3 rows corresponding to CourseId so sequence is 1,2,3 and sequence can be changed with this limit only i.e. 1 can become 2, 2 can become 3 and so on but not greater than 3(max number of rows for a courseID).

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-21 : 07:30:36

Maybe use a Temp table
Something like this:

CREATE TABLE #Temp
(
NewSeqNo INT IDENTITY(1, 1),
ID INT
)
INSERT INTO #Temp
(
ID
)
SELECT
ID
FROM
Deleted
ORDER BY
ID

UPDATE
a
SET Sequence = b.NewSeqNo
FROM
ET_CourseChapter a
INNER JOIN #Temp b
ON a.id = b.id

DROP TABLE #Temp

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 07:30:37

[code]-- prepare test data
declare @t table (Id tinyint, CourseId tinyint, Name varchar(5), Sequence tinyint)

insert @t
select 1, 92, 'Chap1', 1 union all
select 2, 92, 'Chap2', 2 union all
select 3, 92, 'Chap3', 3 union all
select 4, 92, 'Chap4', 4 union all
select 5, 92, 'Chap5', 5 union all
select 6, 92, 'chap6', 6

-- perform
select *
from @t

delete
from @t
where id in (2, 3, 5)

select *
from @t

UPDATE x
SET x.Sequence = (SELECT COUNT(*) FROM @t AS y WHERE y.CourseID = x.CourseID AND y.Sequence <= x.Sequence)
FROM @t AS x

select *
from @t[/code]

Peter Larsson
Helsingborg, Sweden

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-12-21 : 07:44:32

As long as the sequence numbering is intact from the beginning Madhis suggestion would work just fine.

--> Slight alteration of Pesos table:
DECLARE @t TABLE (
  Id TINYINT IDENTITY(1, 1), 
  CourseId TINYINT,
  Name varchar(5), 
  Sequence TINYINT
)

INSERT @t
SELECT 92, 'Chap1', 1 union all
SELECT 92, 'Chap2', 2 union all
SELECT 92, 'Chap3', 3 union all
SELECT 92, 'Chap4', 4 union all
SELECT 92, 'Chap5', 5 union all
SELECT 92, 'chap6', 6

SELECT * FROM @t

--> Do a delete
DECLARE @DeleteSeq int
SET @DeleteSeq = 2
DELETE @t WHERE Sequence = @DeleteSeq
UPDATE @t SET Sequence = Sequence - 1 WHERE Sequence > @DeleteSeq
SELECT * FROM @t

--> Do an insert
DECLARE @InsertSeq int
SET @InsertSeq = 2
UPDATE @t SET Sequence = Sequence + 1 WHERE Sequence >= @InsertSeq
INSERT INTO @t
SELECT 92, 'Chap2', 2

SELECT * FROM @t

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2006-12-21 : 07:48:13

Thanks for all the responses.
Peter, If you could elaborate the logic behind the
UPDATE x
SET x.Sequence = (SELECT COUNT(*) FROM @t AS y WHERE y.CourseID = x.CourseID AND y.Sequence <= x.Sequence)
FROM @t AS x

I am not able to figure out whats happening.Please excuse me for asking this.Also, how efficient this query will be if the table size is large.Thanks again for providing help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-21 : 07:58:54

<<
I am not able to figure out whats happening.Please excuse me for asking this.Also, how efficient this query will be if the table size is large.Thanks again for providing help.
>>

1 That query will generate serail number based on CourseId
2 If the table has millions of data, doing that numbering thru sql will slow down execution timing
3 Still you didnt tell us where you want to show data?

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-12-21 : 08:00:53

Pesos method is more "universal" in the sense that it will always fix everything if someone accidentally inserts/deletes a row without updating the sequence number, but Madhis version (the one in my previous post) is *by far* more efficient. The statement itself is more efficient and the fact that you only have to update the rows with a higher sequence number and not all of them every time will give you a substantial performance gain.

I guess it's a matter of how diciplined you/your developers are when doing insert/updates...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2006-12-21 : 08:04:42

Sorry for not replying to your question Madhivanan. To give whole idea of having sequence column is that user can change the order in which he wants to see the chapter so in order to keep that, I included a sequence field.Data is shown on a web page that allows user to change sequence.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 08:05:26

Also, Madhi's suggestion only works (changing -1 to -2 when deleting two records) when the deleted records are consecutive.
If, somehow, two non-consecutive records are deleted, Madhi's suggestion does not work.

Peter Larsson
Helsingborg, Sweden

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-12-21 : 08:44:03

Very good point peso, sorry for not pointing that out.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 08:49:29

Thanks.

Also, Madhi's suggestion does not take into account it table has several other CourseIDs. ALL sequences are recalculated, over all CourseIDs.
But this I think Madhi left to OP to discover by himself, as a practice.

Peter Larsson
Helsingborg, Sweden

arvind
Starting Member

12 Posts

Posted - 2006-12-22 : 07:58:23

thats good one

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-22 : 08:54:07

quote:
Originally posted by arvind

thats good one

Which one?

Madhivanan

Failing to plan is Planning to fail

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2006-12-26 : 06:18:06

Hi peso,
your query is working perfectly fine. I am totally amazed by the query and so request you to clear one doubt of mine. In your query

UPDATE x
SET x.Sequence = (SELECT COUNT(*) FROM @t AS y WHERE y.CourseID = x.CourseID AND y.Sequence <= x.Sequence)
FROM @t AS x

What I can get is that you have used self join but what I am not able to get is how this query fetches data.Can you please provide some help on this.
Regards

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-26 : 06:28:10

This query is the case of correlated subquery. In simple, this query means,

For each record in first copy of table, give me count of records from second copy whose CourseID matches with those in first copy and whose sequence no. are less than those which are in first copy.

Is that clear? or I made it look more complicated than it is

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-26 : 06:37:20

Is that clear? or I made it look more complicated than it is

Oh man.. you can be a professor..

Aha.. About the logic i Think its an trick to get the sequence number in 2000, since it doesnt have a direct way of getting rownum(like oracle has) though is feature have been introduced in SQL SERVER 2005.

But this query has some performance issue so if this query is going to be used on the frequent basis, then i think you should look for an alternative solutions..

Chirag

http://chirikworld.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources