Remove a 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)

Remove a cursor

Author

Topic

1fred
Posting Yak Master

158 Posts

Posted - 2002-08-27 : 15:31:31

Here is what I'm trying to do, find a random sponsor for a customer. Normally each customer has a sponsor, but sometimes a customer can have no sponsor. To attribute a sponsor to a customer, base on some criterias, I put the customer who can sponsor in a table named TDistributeSponsor. Then I put all the customer who need a sponsor(CustomerSponsor=0) in a cursor and each loop I find him a sponsor with the stored procedure below. Is there a way to do one update with the same criteria as the select in my stored procedure

CREATE TABLE [TDistributeSponsor]
[Customer_key] [int] NOT NULL ,
[Invitation] [int] NULL ,
[Epidemy] [int] NULL ,
[Invite] [int] NULL ,
[Datestamp] [datetime] NOT NULL

Create table LSponsorT
[Customer_key] [int] NOT NULL ,
[CustomerSponsor_key] [int]NULL,
[Datestamp] [datetime] NOT NULL

CREATE PROCEDURE [dbo].[sp_FindSponsor]@customer_key int, @product_key int, @date datetime
AS

declare @customerSponsor_key int

select top 1 @customerSponsor_key = customer_key
from TDistributeSponsor
where datestamp < (@date-2)
and customer_key <> 1
order by newid()

begin transaction xxx
update Lsponsort
set CustomerSponsor_key = @customerSponsor_key
where customer_key = @customer_key
and product_key =@product_key
commit transaction xxx

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-27 : 15:35:47

What does the transaction do

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

1fred
Posting Yak Master

158 Posts

Posted - 2002-08-27 : 15:45:02

It updates my table LSponsorT and put the sponsor(CustomerSponsor) foud with the select statement for the customer. The transaction is part of the stored procedure. I don't want to have to call this stored procedure for each customer in my cursor.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-27 : 18:38:57

I meant why the explicit transaction

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

1fred
Posting Yak Master

158 Posts

Posted - 2002-08-28 : 08:59:49

It is not a good thing to do? This stored procedure can be call like 30 000 in a day. When I do a backup of my transaction log, if the transaction is committed, my log will get reduced. Am I right?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-28 : 09:15:29

begin tran
update ...
commit tran

update tran

Is there any difference?
Unless you have strange database settings.
Implicit transactions would need two commits.
You can only be committing the transaction you started before the update so I don't see any benefit over the transaction the update will run in anyway - it will just slow it down.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

1fred
Posting Yak Master

158 Posts

Posted - 2002-08-28 : 09:46:57

Is there any rules, to use to know when a transaction is useful of not? Before you told me this, I was using a transaction for every stored procedure.

I think they are useful if I want to make a rollback in an insert or update that failed.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-28 : 10:00:59

if an update fails then it won't have updated the database.
Only need transactions for multiple updates

begin tran
update tbl
if @@error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
update tbl2
if @@error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
commit tran

for single statements

update tbl2
if @@error <> 0
begin
raiserror('failed',16,-1)
return
end

is fine.
Every sql statement is atomic - i.e. it either all succeeds or all fails.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources