Problem with temp table in Cursor with update

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)

Problem with temp table in Cursor with update

Author

Topic

happyboy
Starting Member

21 Posts

Posted - 2010-09-13 : 04:35:53

I would like to use cursor with update from a temp table in SQL 2000 but it say "cannot use with readonly" cursor!!!

I have had a stored procedure with a table (not temp table) and the cursor worked rightly. But now I like to use temp table so how to overcome this. Please help me

CREATE TABLE #A (STT int, ND navrchar(2000))

ALTER TABLE #A
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED (STT)

DECLARE mycursor CURSOR FOR
SELECT STT
FROM #A
ORDER STT
FOR UPDATE

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-13 : 05:39:46

Describe what you want to do. In most cases a cursor is not needed.
Maybe we can provide a better solution than using a cursor.

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

happyboy
Starting Member

21 Posts

Posted - 2010-09-13 : 06:08:01

quote:
Originally posted by webfred

Describe what you want to do. In most cases a cursor is not needed.
Maybe we can provide a better solution than using a cursor.

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

I want to go all record one by one and process many things on record

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-13 : 07:26:38

quote:
Originally posted by happyboy

I would like to use cursor with update from a temp table in SQL 2000 but it say "cannot use with readonly" cursor!!!

I have had a stored procedure with a table (not temp table) and the cursor worked rightly. But now I like to use temp table so how to overcome this. Please help me

CREATE TABLE #A (STT int, ND navrchar(2000))

ALTER TABLE #A
ADD CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED (STT)

DECLARE mycursor CURSOR FOR
SELECT STT
FROM #A
ORDER BY STT
FOR UPDATE

The given information is:
- not enough
- wrong

So we can't help

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

happyboy
Starting Member

21 Posts

Posted - 2010-09-14 : 03:13:02

I am sorry for typing mistake. My stored procedure is so long so I just wrote some main line! I consider where I can use cursor with update from a temp table?

Hope to reply soon. Thanks

sathiesh2005
Yak Posting Veteran

85 Posts

Posted - 2010-09-14 : 07:06:53

Hi,
What are you doing inside the cursor?
Please post that also.
or try with specific cursor type.

@Sathiesh

happyboy
Starting Member

21 Posts

Posted - 2010-09-16 : 05:03:20

quote:
Originally posted by sathiesh2005

Hi,
What are you doing inside the cursor?
Please post that also.
or try with specific cursor type.

@Sathiesh

Here is my store procedure. Now it works, but when a lot of user run it at the same time, it insert in same table so the data is not correct so I decide to use temp table (one user -> one table). But the cursor with update cannot use with temp table.

if exists(select * from sysobjects
where name = 'sp_rpt_SaoKeTK')
drop proc sp_rpt_SaoKeTK
GO

CREATE PROCEDURE sp_rpt_SaoKeTK
@TK nvarchar(15),
@TuNgay char(10),
@DenNgay char(10)
AS
BEGIN

DECLARE @DuDau float
SET @DuDau=0

DECLARE @DuDauSK float
SET @DuDauSK=0

DECLARE @DuCuoiSK float
SET @DuCuoiSK=0

SET @DuDau = ( Select dudau+namco-namno
From TL
Where tk=@TK and ngay=DateAdd(d,-1,Convert(DATETIME,@TuNgay,103)))

if (@DuDau is NULL)
SET @DuDau=0

SET @DuDauSK=@DuDau

/*
if exists(select name from tempdb..sysobjects where name='#tmpSaoKeTK') drop table #tmpSaoKeTK

CREATE TABLE #tmpSaoKeTK (
[Ngay] [smalldatetime] NOT NULL ,
[SCT] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[No] [float] NOT NULL ,
[Co] [float] NOT NULL ,
[SoDu] [float] NULL ,
[DienGiai] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TKDoiUng] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Teller] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Seq] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

ALTER TABLE #tmpSaoKeTK ADD
CONSTRAINT [PK_SaoKeTK] PRIMARY KEY CLUSTERED
(
[Ngay],
[SCT],
[No],
[Co]
) WITH FILLFACTOR = 90 ON [PRIMARY]
*/

delete from SaoKeTK

Insert into SaoKeTK
Select a.ngay,(a.teller_id + '.' + a.seq) as SCT,a.[no],a.co,0,b.dg1+b.dg2+b.dg3,
a.tkdu,a.teller_id,a.seq
From DS a left outer join DG b on (a.teller_id=b.[user] and a.seq=b.seq and a.ngay=b.ngay
and a.tktype=b.tktype)
Where a.tk=@TK and
a.ngay between Convert(DATETIME,@TuNgay,103) and Convert(DATETIME,@DenNgay,103)

if ((select count(*) from SaoKeTK) = 0)
Insert into SaoKeTK values(Convert(DATETIME,@DenNgay,103),'','0','0','0',
N'Tài kho?n không phát sinh giao d?ch','','','')

DECLARE @Ngay smalldatetime
DECLARE @SCT nvarchar(50)
DECLARE @No float
SET @No=0
DECLARE @Co float
SET @Co=0
DECLARE @SoDu float
SET @SoDu=0
DECLARE @DienGiai nvarchar(500)
DECLARE @TKDoiUng nvarchar(20)
DECLARE @Teller nvarchar(20)
DECLARE @Seq nvarchar(20)

DECLARE mycursor CURSOR FOR
SELECT Ngay,SCT,[No],Co,SoDu,DienGiai,TKDoiUng,Teller,Seq
FROM SaoKeTK
ORDER BY Ngay,Co desc,[No],SCT
FOR UPDATE

OPEN mycursor

FETCH NEXT FROM mycursor INTO @Ngay,@SCT,@No,@Co,@SoDu,@DienGiai,@TKDoiUng,@Teller,@Seq

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SoDu=@DuDau+@Co-@No

UPDATE SaoKeTK
SET SoDu=@SoDu
WHERE Ngay=@Ngay and SCT=@SCT

SET @DuDau=@SoDu

if (@DienGiai is Null)
begin
update SaoKeTK
set DienGiai=(Select b.dg1+b.dg2+b.dg3
From DG b
Where b.ngay =@Ngay and b.[user]=@Teller and b.seq=@Seq)
where Ngay=@Ngay and SCT=@SCT and [No]=@No and Co=@Co
end

if (@DienGiai is Null and @Teller='DD4400' and @Co <> '0')
begin
update SaoKeTK
set DienGiai= N'Tr? lãi (giao d?ch t? d?ng)'
where Ngay=@Ngay and SCT=@SCT and [No]=@No and Co=@Co
end

if (@DienGiai is Null and @Teller='DD4400' and @No <> '0')
begin
update SaoKeTK
set DienGiai= N'Thu phí qu?n lý tài kho?n (giao d?ch t? d?ng)'
where Ngay=@Ngay and SCT=@SCT and [No]=@No and Co=@Co
end

if (@DienGiai is Null and (@Teller='' or @Teller is Null) and @Co <> '0' and
@TKDoiUng='KC' and day(@Ngay)='31' and month(@Ngay)='12')
begin
update SaoKeTK
set DienGiai= N'Tr? lãi TK ký qu? (giao d?ch t? d?ng)'
where Ngay=@Ngay and SCT=@SCT and [No]=@No and Co=@Co
end

FETCH NEXT FROM mycursor INTO @Ngay,@SCT,@No,@Co,@SoDu,@DienGiai,@TKDoiUng,@Teller,@Seq
END
SET @DuCuoiSK=@SoDu
CLOSE mycursor
DEALLOCATE mycursor

Select *,@DuDauSK as DuDauSK,@DuCuoiSK as DuCuoiSK
From SaoKeTK
Order by ngay,co desc,[no],SCT
END

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-16 : 06:02:04

Throw the code away.

Tell us:

1) The table structure of the table(s) involved.
2) Some sample data that covers all your conditions
3) The required result.

The cursor is terrible. The fact that you are using a base table SaoKeTK to do stuff on is even worse.

SQL is a declarative language -- you need to focus on what you want rather than how to do it.

I think this will condense down to 1 or 2 UPDATE queries that will be nice and set based and will be probably hundreds of times quicker than this.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

happyboy
Starting Member

21 Posts

Posted - 2010-09-16 : 22:25:48

1)My table 'SaoKeTK' is used for statement of "one account" in bank.
The data of this table is got from another table depending on the account that users input
(At the same time, just the data of one account. But now I run on the enviroment that have
multiple users run the stored procedure at the same time). Here is the structure

CREATE TABLE SaoKeTK (
[Ngay] [smalldatetime] NOT NULL ,
[SCT] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[No] [float] NOT NULL ,
[Co] [float] NOT NULL ,
[SoDu] [float] NULL ,
[DienGiai] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TKDoiUng] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Teller] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Seq] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

ALTER TABLE SaoKeTK ADD
CONSTRAINT [PK_SaoKeTK] PRIMARY KEY CLUSTERED
([Ngay],[SCT],[No],[Co])

2)Sample data after select from another table into 'SaoKeTK' and it is order by
Ngay,Co desc,[No],SCT (this order is important for caculating the column 'SoDu',
balance of account after user deposit or withdraw the money)

Ngay SCT No Co SoDu DienGiai TKDoiUng Teller Seq
01/01/10 F313.0001 0 10 0 abc vnd050 F313 1
01/01/10 G999.0030 0 20 0 dfg G999 30
01/01/10 P514.0001 10 0 0 qwe usd050 P514 1
25/01/10 DD4400.01 0 1 0 vnd050 DD4400 1
25/01/10 DD4400.07 10 0 0 vnd020 DD4400 7
31/12/10 DD2760.02 0 10 0 KC DD2760 2

* @DuDauSK: select from another table (for example @Dudau=10)
* @DuCuoiSK=value of last row of column SoDu

* first row: SoDu=@Dudau+co-no
* other row: SoDu=SoDu(row before)+co-no

* for each row I have to chevk and update column 'DienGiai' here is the logic

if (@DienGiai is Null)
begin
update SaoKeTK
set DienGiai=(Select b.dg1+b.dg2+b.dg3
From DG b
Where b.ngay =@Ngay and b.[user]=@Teller and b.seq=@Seq)
where Ngay=@Ngay and SCT=@SCT and [No]=@No and Co=@Co
end

if (@DienGiai is Null and @Teller='DD4400' and @Co <> '0')
begin
update SaoKeTK
set DienGiai= N'Tra Lai'
where Ngay=@Ngay and SCT=@SCT and [No]=@No and Co=@Co
end

if (@DienGiai is Null and @Teller='DD4400' and @No <> '0')
begin
update SaoKeTK
set DienGiai= N'Thu phi'
where Ngay=@Ngay and SCT=@SCT and [No]=@No and Co=@Co
end

if (@DienGiai is Null and (@Teller='' or @Teller is Null) and @Co <> '0' and
@TKDoiUng='KC' and day(@Ngay)='31' and month(@Ngay)='12')
begin
update SaoKeTK
set DienGiai= N'Tra Lai KQ'
where Ngay=@Ngay and SCT=@SCT and [No]=@No and Co=@Co
end

3) result (for example set @DuDauSK=10)

Ngay SCT No Co SoDu DienGiai TKDoiUng Teller Seq DuDauSK DuCuoiSK
01/01/10 F313.0001 0 10 20 abc vnd050 F313 1 10 31
01/01/10 G999.0030 0 20 40 dfg G999 30 10 31
01/01/10 P514.0001 10 0 30 qwe usd050 P514 1 10 31
25/01/10 DD4400.01 0 1 31 Tra Lai vnd050 DD4400 1 10 31
25/01/10 DD4400.07 10 0 21 Thu Phi vnd020 DD4400 7 10 31
31/12/10 DD2760.02 0 10 31 Tra Lai KQ KC DD2760 2 10 31

* the reason why I use cursor is caulate 'SoDu' and update 'DienGiai'

* is there anyway to use cursor with update from temp table. I would like to use temp table
because the data of this table 'SaoKeTK' is correct when one user run and input in it

Thanks for reply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources