Need to 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)

Need to remove a cursor

Author

Topic

1fred
Posting Yak Master

158 Posts

Posted - 2003-05-08 : 13:26:33

Ok here's what I'm doing. I have a system that everyone need to have a sponsor. If someone do not have one, at some time, we find him one random. In a first time I'm inserting in a table all the customer who can be sponsor. Then I loop trough a cursor with all the customer who need a sponsor and call a SP to attribute him a sponsor and update my table. Here's the code. Help is greatly appreciated!

if exists (select * from dbo.sysobjects where id = object_id(N'[TDistributeSponsorT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TDistributeSponsorT]

CREATE TABLE [TDistributeSponsorT] (
[Customer_key] [int] NOT NULL ,
[Invitation] [int] NULL ,
[Epidemy] [int] NULL ,
[Invite] [int] NULL ,
[Datestamp] [datetime] NOT NULL ,
CONSTRAINT [PK_TDistributeSponsorT] PRIMARY KEY CLUSTERED
(
[Customer_key]
) ON [PRIMARY]
) ON [PRIMARY]

insert into TDistributeSponsorT
/*cette requête a été créée en utilisant le programme VB de redistribution des sponsors*/
/*pour modifier les critères vous pouvez utiliser ce programme*/
/*pour le moment, le critères sont:
produit = 1
invité min = 1
invitation min = 1
*/
SELECT
Lcustomert.customer_key,
COUNT(LsentT.sent_key) AS invitation,
Lsponsort.epidemy,
frederick.LNumberInviteV.Invité,
LsponsorT.datestamp
FROM
frederick.LNumberInviteV RIGHT OUTER JOIN
dbo.LSponsorT ON frederick.LNumberInviteV.Product_key = dbo.LSponsorT.Product_key RIGHT OUTER JOIN
dbo.LCustomerT ON frederick.LNumberInviteV.Customer_key = dbo.LCustomerT.Customer_key
AND dbo.LSponsorT.Customer_key = dbo.LCustomerT.Customer_key LEFT OUTER JOIN
dbo.LCountryT ON dbo.LCustomerT.Country_key = dbo.LCountryT.Country_key LEFT OUTER JOIN
dbo.LSentT ON dbo.LCountryT.Country_key = dbo.LSentT.Country_key
AND dbo.LCustomerT.Customer_key = dbo.LSentT.Customer_key
where
Lsponsort.product_key = 1
and LsentT.product_key = 1
GROUP BY
Lcustomert.customer_key,
Lsponsort.epidemy ,
frederick.LNumberInviteV.Invite,
LsponsorT.datestamp
having
COUNT(LsentT.sent_key) >= 1
and frederick.LNumberInviteV.Invite >= 1
order by lcustomert.customer_key

/* on a inséré les données dans une table temporaire. Tous les customers dans cette table
peuvent être sponsor pour un consommateur sans sponsor */
--on sélectionne tous les customers sans sponsors
declare @customer_key int --le customer_key
declare @product_key int --le product_key
declare @datestamp datetime --la date d'inscription pour ce produit

update lsponsort
set CustomerSponsor_key = 0
where customerSponsor_key is null

declare NoSponsor_cur cursor for
select customer_key, product_key, datestamp from LsponsorT where CustomerSponsor_key = 0 and product_key =1

OPEN NoSponsor_cur
FETCH NEXT FROM NoSponsor_cur
INTO @customer_key,@product_key,@datestamp
WHILE @@FETCH_STATUS = 0
BEGIN

exec frederick.sp_trouverSponsor @customer_key,@product_key,@datestamp

FETCH NEXT FROM NoSponsor_cur
INTO @customer_key,@product_key,@datestamp
END
CLOSE NoSponsor_cur
DEALLOCATE NoSponsor_cur

Create PROCEDURE [frederick].[sp_TrouverSponsor] @customer_key int, @product_key int, @date datetime
AS

declare @customerSponsor_key int

select top 1 @customerSponsor_key = customer_key
from TDistributeSponsorT
where datestamp < (@date-2)
and customer_key <> 1 and customer_key in(select customer_key from cvisittotalt where employee_key is null)
order by newid()

update Lsponsort
set CustomerSponsor_key = @customerSponsor_key, sponsoringType_key = 5
where customer_key = @customer_key
and product_key =@product_key

1fred
Posting Yak Master

158 Posts

Posted - 2003-05-09 : 13:03:56

Ok during my good sleep I found a solution. For those interested, I'll insert my customer in need of a sponsor in a table, and in another table I'll insert the same number of customer who can sponsor them. Then I'll juste have to update the table with my customer whith the value of the sponsor on the same rowID.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-05-09 : 14:55:13

Can you post your code?

Jung

1fred
Posting Yak Master

158 Posts

Posted - 2003-05-09 : 14:57:43

It is not over but 90% of the hard work is there.

if exists (select * from dbo.sysobjects where id = object_id(N'[TDistributeSponsorT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TDistributeSponsorT]

CREATE TABLE [TDistributeSponsorT] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Customer_key] [int] NOT NULL ,
[Invitation] [int] NULL ,
[Epidemy] [int] NULL ,
[Invite] [int] NULL ,
[Datestamp] [datetime] NOT NULL ,
CONSTRAINT [PK_TDistributeSponsorT] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]

declare @nbCustomer int
declare @nbLigne int

update lsponsort set customerSponsor_key = 0 where customerSPonsor_key is null

select @nbCustomer count(customer_key) from lSponsorT where customerSponsor_key = 0
set @nbLigne = 0

while @nbLigne < @nbCustomer
begin
insert into TDistributeSponsorT(customer_key,Invitation,epidemy,Invite,Datestamp)
/*cette requête a été créée en utilisant le programme VB de redistribution des sponsors*/
/*pour modifier les critères vous pouvez utiliser ce programme*/
/*pour le moment, le critères sont:
produit = 1
invité min = 1
invitation min = 1
*/
SELECT
Lcustomert.customer_key,
COUNT(LsentT.sent_key) AS invitation,
Lsponsort.epidemy,
frederick.LNumberInviteV.Invité,
LsponsorT.datestamp
FROM
frederick.LNumberInviteV RIGHT OUTER JOIN
dbo.LSponsorT ON frederick.LNumberInviteV.Product_key = dbo.LSponsorT.Product_key RIGHT OUTER JOIN
dbo.LCustomerT ON frederick.LNumberInviteV.Customer_key = dbo.LCustomerT.Customer_key
AND dbo.LSponsorT.Customer_key = dbo.LCustomerT.Customer_key LEFT OUTER JOIN
dbo.LCountryT ON dbo.LCustomerT.Country_key = dbo.LCountryT.Country_key LEFT OUTER JOIN
dbo.LSentT ON dbo.LCountryT.Country_key = dbo.LSentT.Country_key
AND dbo.LCustomerT.Customer_key = dbo.LSentT.Customer_key
where
Lsponsort.product_key = 1
and LsentT.product_key = 1
GROUP BY
Lcustomert.customer_key,
Lsponsort.epidemy ,
frederick.LNumberInviteV.Invite,
LsponsorT.datestamp
having
COUNT(LsentT.sent_key) >= 1
and frederick.LNumberInviteV.Invite >= 1
order by newid()

select @nbLigne = count(id) from TDistributeSponsorT

end

if exists (select * from dbo.sysobjects where id = object_id(N'[TCustomerT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TCustomerT]

CREATE TABLE [TCustomerT] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Customer_key] [int] NOT NULL ,
[CustomerSponsor_key] [int] NULL ,
[Datestamp] [datetime] NOT NULL ,
CONSTRAINT [PK_TCustomerT] PRIMARY KEY CLUSTERED
(
[ID],
[customer_key]
) ON [PRIMARY]
) ON [PRIMARY]

insert into TCustomerT(Customer_key,CustomerSponsor_key,Datestamp)
select Customer_key,CustomerSponsor_key,datestamp from LSponsorT where CustomerSPonsor_key = 0

update TCustomerT
set CustomerSponsor_key = TDistributeSponsorT.Customer_key
from TDistributeSponsorT
where TDistributeSponsorT.id = TcustomerT.id
and TDistributeSponsorT.datestamp > (tcustomerT.datestamp+2)

1fred
Posting Yak Master

158 Posts

Posted - 2003-05-09 : 23:50:53

For what I had to do, the first script was running for 13 hours and the second for ONLY 40 minutes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources