Iteration without 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

Transact-SQL (2000)

Iteration without cursor

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-11-17 : 11:14:29

We are using cursor in one of our stored procedure, where we need to iterate through the records to update each record. Is there any better way to iterate through the records without using cursors?

Thanks

Karunakaran

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-17 : 11:24:18

More than likely there is a better way, but since you didn't post any details at all about what you are trying to do, there is no way for us to answer that question.

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-17 : 11:26:40

True enough....my esp usb port is clogged..follow the instructions in the hint link in my sig...

How aboput posting the actual code as well

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-11-17 : 12:21:03

Will this help? This is not a full piece of table, but I think its relevent to our discussion.If more info needed let me know.


create table #Temp (LName varchar(255),Cid int,Pid int,PmtId int,CurrentRecord int,TotalRecord int)

insert into #Temp
select  'abc',123,123,7879,NULL,3
union
select 'abc',123,234,8799,NULL,3
union
select 'abc',123,456,987,NULL,3
union
select 'xyz',456,001,234,NULL,4
union
select 'xyz',456,345,1234,NULL,4
union
select 'xyz',456,456,5678,NULL,4
union
select 'xyz',456,789,6789,NULL,4

-- Cursor.

DECLARE UpdateCurrentPage CURSOR FOR         
SELECT CID, LName, PID,PmtId  
FROM #Temp
ORDER BY PmtId -- Selecting the records from table #Temp
        
DECLARE @CID INT        
DECLARE @LName VARCHAR(255)        
DECLARE @PID INT        
DECLARE @IncValue INT  
DECLARE @PmtId INT        
        
SET @IncValue = 0        
        
OPEN UpdateCurrentPage        
        
FETCH NEXT FROM UpdateCurrentPage         
INTO @CID, @LName, @PID,@PmtId  
        
WHILE @@FETCH_STATUS = 0        
BEGIN  
--exec test_kk @PmtID -- This SP I need to call in whatever Iteration logic I use.
 UPDATE #Temp        
 SET @incvalue = CurrentRecord = (case @incvalue when 8 then 1 else @incvalue+1 end)        
 WHERE cid = @cid and lname = @lname and pid = @pid        
        
 SET @incvalue = 0        
     
   FETCH NEXT FROM UpdateCurrentPage         
 INTO @CID, @LName, @PID,@PmtId  
END        
        
CLOSE UpdateCurrentPage        
DEALLOCATE UpdateCurrentPage

The current result is something like this:


LName      Cid         Pid         PmtId       CurrentRecord TotalRecord 
---------- ----------- ----------- ----------- ------------- ----------- 
abc        123         123         7879        1             3
abc        123         234         8799        1             3
abc        123         456         987         1             3
xyz        456         1           234         1             4
xyz        456         345         1234        1             4
xyz        456         456         5678        1             4
xyz        456         789         6789        1             4

What I need is


LName      Cid         Pid         PmtId       CurrentRecord TotalRecord 
---------- ----------- ----------- ----------- ------------- ----------- 
abc        123         123         7879        1             3
abc        123         234         8799        2             3
abc        123         456         987         3             3
xyz        456         1           234         1             4
xyz        456         345         1234        2             4
xyz        456         456         5678        3             4
xyz        456         789         6789        4             4

The PID and PmtId columns will be unique ones.

Thanks

Karunakaran

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-17 : 12:43:19

Hmm.

my preferred way to avoid cursors is using a Table type with a seq number (identity column)
and use a while loop to loop the table and perform the cursor like operations if you can't do it in sets.

but, at a quick glance you should be able to do an Update without looping the table. (do it in a set)

________________________________________________

Drinking German Beer... fun.
Listening to an accordian player play ACDC...priceless

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-17 : 14:42:57

This should do it:


drop table #temp
drop table #temp2
drop table #temp3

go

create table #Temp 
(
	LName varchar(5),
	Cid int,
	Pid int,
	PmtId int,
	CurrentRecord int,
	TotalRecord int
)
create table #Temp2 
(
	Seq	int identity(1,1),
	LName varchar(5),
	Cid int,
	Pid int,
	PmtId int,
	CurrentRecord int,
	TotalRecord int,
)
create table #Temp3 
(
	LName varchar(5),
	Cid int,
	Pid int,
	PmtId int,
	CurrentRecord int,
	TotalRecord int
)

insert into #Temp
select  'abc',123,123,7879,NULL,3
union
select 'abc',123,234,8799,NULL,3
union
select 'abc',123,456,987,NULL,3
union
select 'xyz',456,001,234,NULL,4
union
select 'xyz',456,345,1234,NULL,4
union
select 'xyz',456,456,5678,NULL,4
union
select 'xyz',456,789,6789,NULL,4

-- Load temp table with a row sequence number
insert into #temp2
	(
	LName,
	Cid,
	Pid,
	PmtId,
	CurrentRecord,
	TotalRecord
	)
select
	top 100 percent
	*
from
	#temp
order by
	Cid,
	Pid,
	PmtId

insert into #temp3
select
	a.LName,
	a.Cid,
	a.Pid,
	a.PmtId,
	-- Calculate Current Record Number
	CurrentRecord = a.Seq-b.min_seq+1,
	TotalRecord
from
	#temp2 a
	join
	(
	-- Get Min seq # for each Cid
	select
		top 100 percent
		bb.Cid,
		min_seq = min(bb.seq)
	from
		#temp2 bb
	group by
		bb.Cid
	order by
		bb.Cid
	)b
	on a.Cid = b.Cid	
order by
	a.Cid,
	a.Pid,
	a.PmtId

print '#temp'
select * from #temp
print '#temp3'
select * from #temp3



#temp
LName Cid         Pid         PmtId       CurrentRecord TotalRecord 
----- ----------- ----------- ----------- ------------- ----------- 
abc   123         123         7879        NULL          3
abc   123         234         8799        NULL          3
abc   123         456         987         NULL          3
xyz   456         1           234         NULL          4
xyz   456         345         1234        NULL          4
xyz   456         456         5678        NULL          4
xyz   456         789         6789        NULL          4

(7 row(s) affected)

#temp3
LName Cid         Pid         PmtId       CurrentRecord TotalRecord 
----- ----------- ----------- ----------- ------------- ----------- 
abc   123         123         7879        1             3
abc   123         234         8799        2             3
abc   123         456         987         3             3
xyz   456         1           234         1             4
xyz   456         345         1234        2             4
xyz   456         456         5678        3             4
xyz   456         789         6789        4             4

(7 row(s) affected)

CODO ERGO SUM

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-11-17 : 15:05:32

[code]select lname,cid,pid,pmtid,
( select count(cid) from #temp t2 where t2.cid = t1.cid and t2.pid <= t1.pid ) currentRecord,
totalRecord
from
#temp t1
order by
lname,cid,pid

[/code]

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-17 : 15:25:34

quote:
my preferred way to avoid cursors is using a Table type with a seq number (identity column)
and use a while loop to loop the table and perform the cursor like operations if you can't do it in sets.

...what makes you think that this is going to be any faster than a cursor? It is not the cursors that are slow, it is using looping (loopy?) logic instead of set-based transactions.

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-11-18 : 00:16:13

Thanks for the solutions..
Both solutions from MVJ and ehorn works fine. I'm not sure which is an optimal way...

Since I need to update the currentrecord row in #Temp I added this to ehorn's solution


create table #Temp2
(
	LName varchar(5),
	Cid int,
	Pid int,
	PmtId int,
	CurrentRecord int,
	TotalRecord int
)
insert into #temp2
select lname,cid,pid,pmtid,
	(select count(cid) from #temp t2 where t2.cid = t1.cid and 
t2.pid <= t1.pid) currentRecord,
	totalRecord
from
	#temp t1
order by 
	lname,cid,pid
update #temp
set #temp.currentrecord =  t2.currentrecord from #temp inner join 
#Temp2 t2  on  #temp.lname = t2.lname and #temp.cid = t2.cid and 
#temp.pid=t2.pid and #temp.pmtid=t2.pmtid

Well, thats not the problem atleast for now...I think I failed to explain this part earlier and didnt make it bold or something..


--exec test_kk @PmtID -- This SP I need to call in whatever Iteration logic I use.
 UPDATE #Temp        
 SET @incvalue = CurrentRecord = (case @incvalue when 8 then 1 else @incvalue+1 end)        
 WHERE cid = @cid and lname = @lname and pid = @pid

The case statement is used in a scenario where the totalrecord is more than 8, in that case after the current record reaches 8, we reset it back to 1. so for 9 records the 9th record will have the value of 1 for currentrecord column and so on...


LName Cid         Pid         PmtId       CurrentRecord TotalRecord 
----- ----------- ----------- ----------- ------------- ----------- 
xyz   456         1           234         1             10
xyz   456         345         1234        2             10
.....
.....
xyz   456         456         5678        8             10
xyz   456         789         6789        1             10
xyz   456         007         9999        2             10

Regarding to the that SP marked bold it is similar to this post here.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57635

Thanks

Karunakaran

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-18 : 01:31:57

Also refer point1 here
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-11-18 : 02:19:38

quote:
Originally posted by madhivanan

Also refer point1 here
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

When I tried to execute that code I get error on these lines...


SET  @cnt = rank =  case when 
     exists (select top 1 id from @table where id not exists
 (select top 1 id from @table where id=t1.id and count1 > t1.count1)
 then 1

Thanks

Karunakaran

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-18 : 03:09:26

Here is a corrected code


  declare @table1 table (id int, name varchar(50))
  insert into @table1
  select null, 'text1' union all
  select null, 'text2' union all
  select null, 'text3' union all
  select null, 'text4'
  select * from @table1 
  declare @inc int
  set @inc = 0

  UPDATE @table1 SET @inc = id = @inc + 1
  select * from @table1 

  declare @table table (id int, diag int, count1 int, rank int)
  insert into @table
  select 1, 42, 75, null union all
  select 1, 49, 50, null union all 
  select 1, 38, 22, null union all
  select 2, 70, 48, null union all
  select 2, 33, 27, null union all
  select 2, 30, 12, null union all
  select 2, 34, 5, null union all
  select 2, 54, 3, null union all
  select 3, 42, 75, null union all
  select 3, 49, 50, null union all 
  select 3, 38, 22, null 

  declare @cnt int
  set @cnt = 0
  UPDATE  t1
  SET  @cnt = rank =  case when 
     exists (select top 1 id from @table where     
	not exists (select top 1 id from @table where id=t1.id and count1 > t1.count1))
     then 1
     else @cnt + 1
     end
  from @table t1
  select * from @table

Madhivanan

Failing to plan is Planning to fail

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-11-18 : 04:03:20

quote:
Originally posted by madhivanan

Here is a corrected code

SET @cnt = rank = case when
exists (select top 1 id from @table where
not exists (select top 1 id from @table where id=t1.id and count1 > t1.count1)) then 1

Madhivanan

Failing to plan is Planning to fail

I see that you have closed ")" at the end. Which I also tried, still I got an error..

...Now I dont get error in the corrected code of yours?... what was that I overlooked?

Thanks

Karunakaran

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-18 : 04:13:36

Here is the change that you need to do

SET @cnt = rank = case when
exists (select top 1 id from @table where id not exists
(select top 1 id from @table where id=t1.id and count1 > t1.count1))
then 1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources