CODEWITHSUNDEEP
sql-serverstored-procedures
user10605996
user10605996

Reputation: 37

Stored procedure only executes correctly on first execution

I've written this SQL Server stored procedure that inserts records into another table based on the order frequency of customers in another table. It assigns a rank to each customer based on their order frequency. When I create the procedure and execute it for the first time, it works fine and inserts the correct records into the table. But when I clear the table and try to execute the procedure again, no records are added. I have to delete the procedure, restart SSMS, and create the procedure again for it to work correctly again.

Here is the procedure:

create procedure TopKCustomer (@CustRank decimal(11,0))
as
    declare CustCursor cursor local for
        select o.CustomerID,c.CustomerName,c.CustomerPostalCode,
            count(o.CustomerID) as 'Order Frequency'
        from (Customer_T c join Order_T o on c.CustomerID=o.CustomerID) 
        group by o.CustomerID,c.CustomerName,c.CustomerPostalCode
        order by [Order Frequency] desc;

    declare @PrevOrderFreq float;
    declare @CurrOrderFreq float;
    declare @CurrRank decimal(11,0);
    declare @CurrCustID decimal(11,0);
    declare @CurrCustName varchar(25);
    declare @CurrCustPostCode varchar(10);

begin
    set @PrevOrderFreq = 0;
    set @CurrOrderFreq = 0;
    set @CurrRank = 0;
    set @CurrCustID = 0;
    set @CurrCustName = '';
    set @CurrCustPostCode = '';

    open CustCursor;

    while @@FETCH_STATUS = 0
    begin
        fetch next from CustCursor into @CurrCustID, @CurrCustName, @CurrCustPostCode, @CurrOrderFreq;

        if @CurrOrderFreq <> @PrevOrderFreq
        begin
            set @CurrRank = (@CurrRank + 1);

            if @CurrRank > @CustRank
            begin
                break;
            end
        end

        insert into TopKCustomer_T
        values (@CurrCustID, @CurrCustName, @CurrCustPostCode, @CurrRank, getdate());

        set @PrevOrderFreq = @CurrOrderFreq;
    end

    close CustCursor;
    deallocate CustCursor;
end

Here are the tables I'm working with:

Upvotes: 0

Views: 558

Answers (2)

George Barwood
George Barwood

Reputation: 219

I think the problem is

while @@FETCH_STATUS = 0

This will be result of the previous fetch (in other words the fetch from the previous execution of your stored procedure, not what you want).

The usual way I wrote cursor loops is

while 1 =1 
begin
  fetch next from c into ...
  if @@fetch_status != 0 break 
  ...
end

Upvotes: 2

Eric
Eric

Reputation: 3257

There's no sample data or table structure so I don't know what your data looks like. Below is what I think you want. The inner query count the order per customer. The outer query rank them.

SELECT *
    , DENSE_RANK() OVER(PARTITION BY CustomerID ORDER BY OrderFrequency) AS Rnk
FROM (
    SELECT *
        , COUNT(*) OVER (PARTITION BY o.CustomerID) AS OrderFrequency
    FROM Customer_T c 
    JOIN Order_T o ON c.CustomerID = o.CustomerID
) a

Upvotes: 1

Related Questions