CODEWITHSUNDEEP
sqlsql-servercursor
user1809104
user1809104

Reputation: 717

Can this cursor in SQL Server 2008 be refactored into something "set based"

DECLARE @PaymentTime VARCHAR (50)

DECLARE @DueDate VARCHAR (50)
DECLARE @CustomerID int

DECLARE MYCURSOR5 CURSOR 
FOR SELECT   distinct  Payment.PaymentTime,Invoice.DueDate, Customer.CustomerID
FROM         Cus_Cred_Terms INNER JOIN
                      Customer ON Cus_Cred_Terms.CustomerID = Customer.CustomerID INNER JOIN
                      Payment ON Customer.CustomerID = Payment.CustomerID INNER JOIN
                      Invoice ON Payment.InvoiceID = Invoice.InvoiceID


open MYCURSOR5 

FETCH MYCURSOR5 INTO @PaymentTime, @DueDate, @CustomerID

WHILE @@FETCH_STATUS = 0
BEGIN 


if(@PaymentTime > @DueDate)
begin
print'payment time:'
PRINT @PaymentTime 
print'due date:'
PRINT @DueDate 

print''
print @CustomerID
print''
update dbo.Cus_Cred_Terms
set dbo.Cus_Cred_Terms.CreditAllowed = 0
FROM Cus_Cred_Terms INNER JOIN
 Customer ON Cus_Cred_Terms.CustomerID = Customer.CustomerID INNER JOIN
 Payment ON Customer.CustomerID = Payment.CustomerID
 where Customer.CustomerID = @CustomerID and dbo.Cus_Cred_Terms.CreditAllowed = 1
end



FETCH MYCURSOR5  INTO @PaymentTime, @DueDate, @CustomerID

END
CLOSE MYCURSOR5

It loops through each field checking if the DueDate is smaller than the actual PaymentDate and if it is, then it sets a field in another table to 0 (1 for credit allowed and 0 for credit not allowed)

So the question is, can I avoid using a cursor to do this? If you need more information please ask.

Upvotes: 2

Views: 145

Answers (1)

marc_s
marc_s

Reputation: 754398

Try something like this:

;WITH CTE AS 
(
    SELECT DISTINCT  
        Customer.CustomerID
    FROM         
        Cus_Cred_Terms 
    INNER JOIN
        Customer ON Cus_Cred_Terms.CustomerID = Customer.CustomerID 
    INNER JOIN
        Payment ON Customer.CustomerID = Payment.CustomerID 
    INNER JOIN
        Invoice ON Payment.InvoiceID = Invoice.InvoiceID
    WHERE
        Payment.PaymentTime > Invoice.DueDate
)
UPDATE
    dbo.Cus_Cred_Terms
SET
    dbo.Cus_Cred_Terms.CreditAllowed = 0
FROM 
    CTE 
WHERE 
    dbo.Cus_Cred_Terms.CustomerID = CTE.CustomerID
    AND dbo.Cus_Cred_Terms.CreditAllowed = 1

The CTE (Common Table Expression) basically has the SELECT that your cursor had - except it also check the constraint Payment.PaymentTime > Invoice.DueDate. Only those customer ID's that match this criteria are selected.

From those CustomerID values, the table dbo.Cus_Cred_Terms is updated if the CreditAllowed column for this CustomerID in that table is equal to 1.

Upvotes: 1

Related Questions