Cursor problems

Hello,
I have the following statements:
declare @.invoice_date smalldatetime
declare @.due_date smalldatetime
declare @.duration int
declare calculate_date cursor forward_only read_only
for
select data, due_date from data1
open calculate_date
fetch next from calculate_date into @.invoice_date, @.duration
while @.@.fetch_status = 0
begin
set @.due_date = dateadd(month, @.duration, @.invoice_date)
--print @.due_date
--print @.invoice_date
--print @.duration
insert into data1 (expire_date) values (@.due_date)
fetch next from calculate_date into @.invoice_date, @.duration
end
close calculate_date
deallocate calculate_date
--truncate table data1
--select count (*) from data1
It's pretty simple, I want to compute the due date of a loan based on it's
release date and it's duration.
Here the ddl for the table
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[data1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[data1]
GO
CREATE TABLE [dbo].[data1] (
[data] [smalldatetime] NULL ,
[due_date] [int] NULL ,
[expire_date] [smalldatetime] NULL
) ON [PRIMARY]
GO
demo data:
Insert into data1(data, due_date) values (28/04/2002,22)
---
Instead of inserting the @.due_date on the corresponding line of the current
date, it inserts it in on the next line...plus lots of null values.
Now, this whole approach works by using the expire_date as a computed column
((dateadd(month,[due_date],[data]))) ...but I would like to use the cursor.
Thans,
TudorIf I've understood correctly, I believe you want to be doing an update on
[data1] rather than in insert. I think you should be doing an update in you
r
cursor using the [WHERE CURRENT OF] clause.
If this sounds like what you're trying to achieve and would like a code
example, let me know and I'll post some code up.
--
Adam J Warne, MCDBA
"Tudor Sofron" wrote:

> Hello,
> I have the following statements:
> declare @.invoice_date smalldatetime
> declare @.due_date smalldatetime
> declare @.duration int
> declare calculate_date cursor forward_only read_only
> for
> select data, due_date from data1
> open calculate_date
> fetch next from calculate_date into @.invoice_date, @.duration
> while @.@.fetch_status = 0
> begin
> set @.due_date = dateadd(month, @.duration, @.invoice_date)
> --print @.due_date
> --print @.invoice_date
> --print @.duration
> insert into data1 (expire_date) values (@.due_date)
> fetch next from calculate_date into @.invoice_date, @.duration
> end
> close calculate_date
> deallocate calculate_date
> --truncate table data1
> --select count (*) from data1
> It's pretty simple, I want to compute the due date of a loan based on it's
> release date and it's duration.
> Here the ddl for the table
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[data1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[data1]
> GO
> CREATE TABLE [dbo].[data1] (
> [data] [smalldatetime] NULL ,
> [due_date] [int] NULL ,
> [expire_date] [smalldatetime] NULL
> ) ON [PRIMARY]
> GO
> demo data:
> Insert into data1(data, due_date) values (28/04/2002,22)
> ---
>
> Instead of inserting the @.due_date on the corresponding line of the curren
t
> date, it inserts it in on the next line...plus lots of null values.
> Now, this whole approach works by using the expire_date as a computed colu
mn
> ((dateadd(month,[due_date],[data]))) ...but I would like to use the cursor.
> Thans,
> Tudor
>
>
>|||It looks like you just need an UPDATE statement:
UPDATE data1
SET expire_date = DATEADD(MONTH,due_date,data) ;
It's a mistake to store both the duration and the expiry date if one is
derived from the other. In general, don't store calculated results in
the database at all.
If you are new to SQL Server then stay away from cursors. Cursors are
rarely a good idea and are almost never necessary.
David Portas
SQL Server MVP
--|||Or if you wanted to do the update without a cursor (increase in performance)
you can just use:
--CODE START
update
data1
set
expire_date = dateadd(month,[due_date],[data])
--CODE END
But I read your problem as you wanted to use a cursor.
--
Adam J Warne, MCDBA
"Tudor Sofron" wrote:

> Hello,
> I have the following statements:
> declare @.invoice_date smalldatetime
> declare @.due_date smalldatetime
> declare @.duration int
> declare calculate_date cursor forward_only read_only
> for
> select data, due_date from data1
> open calculate_date
> fetch next from calculate_date into @.invoice_date, @.duration
> while @.@.fetch_status = 0
> begin
> set @.due_date = dateadd(month, @.duration, @.invoice_date)
> --print @.due_date
> --print @.invoice_date
> --print @.duration
> insert into data1 (expire_date) values (@.due_date)
> fetch next from calculate_date into @.invoice_date, @.duration
> end
> close calculate_date
> deallocate calculate_date
> --truncate table data1
> --select count (*) from data1
> It's pretty simple, I want to compute the due date of a loan based on it's
> release date and it's duration.
> Here the ddl for the table
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[data1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[data1]
> GO
> CREATE TABLE [dbo].[data1] (
> [data] [smalldatetime] NULL ,
> [due_date] [int] NULL ,
> [expire_date] [smalldatetime] NULL
> ) ON [PRIMARY]
> GO
> demo data:
> Insert into data1(data, due_date) values (28/04/2002,22)
> ---
>
> Instead of inserting the @.due_date on the corresponding line of the curren
t
> date, it inserts it in on the next line...plus lots of null values.
> Now, this whole approach works by using the expire_date as a computed colu
mn
> ((dateadd(month,[due_date],[data]))) ...but I would like to use the cursor.
> Thans,
> Tudor
>
>
>|||First of all, data1 doesn't have a key. Every table should have a key.
There are only a few exceptions to this rule. The only one I can think of
offhand is a table that is used to store an intermediate result set during a
long-running calculation.
Unless there is a very compelling reason to use one, I always recommend
against using a cursor. That said, I will give you a few pointers:
(1) Use a local cursor. Local cursors are automagically deallocated when
they go out of scope. An error can occur that terminates a batch, thus
bypassing the explicit deallocate. Note: you should always explicitly close
and deallocate a cursor immediately after you've finished with it to free up
any resources used by it.
DECLARE @.calculate_date CURSOR
SET @.calculate_date = CURSOR LOCAL FAST_FORWARD FOR SELECT ...
(2) cache results in a table variable so you can commit the changes in a
single set-based statement.
(3) Add a rowversion (timestamp) column to your tables so that you can use
optimistic concurrency with rowversioning. This allows long-running
calculations to perform most of their work using a read uncommitted
transaction isolation level (no locks). When you're ready to commit the
changes, start a transaction, re-read the rows used to calculate the results
using WITH(REPEATABLEREAD) or WITH(SERIALIZABLE) and those that need to be
updated using WITH(UPDLOCK), and issue the updates only if none of the
rowversions have changed. Collisions usually occur infrequently, so the
overall performance impact of restarting an update process because a row was
changed by another user is usually minimal.
The above steps minimize the performance and scalability issues surrounding
the use of cursors because triggers only fire once per updated table,
indexes need only be updated once, less information is written to the
transaction log, and locks aren't held any longer than is absolutely
necessary.
"Tudor Sofron" <tsofron@.hopscotch.com> wrote in message
news:OzuZi3XoFHA.3316@.tk2msftngp13.phx.gbl...
> Hello,
> I have the following statements:
> declare @.invoice_date smalldatetime
> declare @.due_date smalldatetime
> declare @.duration int
> declare calculate_date cursor forward_only read_only
> for
> select data, due_date from data1
> open calculate_date
> fetch next from calculate_date into @.invoice_date, @.duration
> while @.@.fetch_status = 0
> begin
> set @.due_date = dateadd(month, @.duration, @.invoice_date)
> --print @.due_date
> --print @.invoice_date
> --print @.duration
> insert into data1 (expire_date) values (@.due_date)
> fetch next from calculate_date into @.invoice_date, @.duration
> end
> close calculate_date
> deallocate calculate_date
> --truncate table data1
> --select count (*) from data1
> It's pretty simple, I want to compute the due date of a loan based on it's
> release date and it's duration.
> Here the ddl for the table
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[data1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[data1]
> GO
> CREATE TABLE [dbo].[data1] (
> [data] [smalldatetime] NULL ,
> [due_date] [int] NULL ,
> [expire_date] [smalldatetime] NULL
> ) ON [PRIMARY]
> GO
> demo data:
> Insert into data1(data, due_date) values (28/04/2002,22)
> ---
>
> Instead of inserting the @.due_date on the corresponding line of the
current
> date, it inserts it in on the next line...plus lots of null values.
> Now, this whole approach works by using the expire_date as a computed
column
> ((dateadd(month,[due_date],[data]))) ...but I would like to use the
cursor.
> Thans,
> Tudor
>
>|||Hello and thanks for your replies.
Well, this sort of script is going to be used only for data generation for a
test datawarehouse, not in a production enviroment :-) Id's arent't
necessary since I don't care how the data is loaded.
So, basically, I don't care how long time it will run :-).
The batch works fine with
update data set expire_date = @.due_date where current of calculate_date
So, thank you all for your answers.
Tudor
"Adam Warne" <AdamWarne@.discussions.microsoft.com> wrote in message
news:95FD1060-7EE6-4D79-A557-68ABF69AE2EB@.microsoft.com...
> If I've understood correctly, I believe you want to be doing an update on
> [data1] rather than in insert. I think you should be doing an update in
> your
> cursor using the [WHERE CURRENT OF] clause.
> If this sounds like what you're trying to achieve and would like a code
> example, let me know and I'll post some code up.
> --
> Adam J Warne, MCDBA
>
> "Tudor Sofron" wrote:
>