| Author |
 Topic  |
|
|
karuna
Aged Yak Warrior
581 Posts |
 Posted - 11/17/2005 : 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)
USA
7007 Posts |
Posted - 11/17/2005 : 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 |
|
|
karuna
Aged Yak Warrior
581 Posts |
Posted - 11/17/2005 : 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
USA
298 Posts |
Posted - 11/17/2005 : 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)
USA
7007 Posts |
Posted - 11/17/2005 : 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
Flowing Fount of Yak Knowledge
USA
1629 Posts |
Posted - 11/17/2005 : 15:05:32
|
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
|
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 11/17/2005 : 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
581 Posts |
Posted - 11/18/2005 : 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 |
Edited by - karuna on 11/18/2005 00:26:10 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
|
|
karuna
Aged Yak Warrior
581 Posts |
Posted - 11/18/2005 : 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 |
Edited by - karuna on 11/18/2005 02:24:47 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 11/18/2005 : 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
581 Posts |
Posted - 11/18/2005 : 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
India
22469 Posts |
Posted - 11/18/2005 : 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 |
|
|
| |
Topic |
|
|
|
Iteration without cursor
