SQL Server stored procedure breaking up results-CodePudding

I am trying to get a list back from a stored procedure, but I believe I may have used the wrong method?

The data I am getting back is fine, but is breaking up the results into sections instead of one continuous result.

I need it to be in one continuous result as it needs to be then exported out to an accounting program.

Here is my code :

CREATE PROCEDURE [dbo].[billing_generate_invoice]
    @varBillingDealerPeriodID int
AS
    DECLARE @BillingDealerBatchRosterID int;
  
    DECLARE MyCursor CURSOR LOCAL FOR
        SELECT BillingDealerBatchRosterID
        FROM dbo.billing_dealer_batch_roster
        WHERE BillingDealerPeriodID = @varBillingDealerPeriodID;

    OPEN MyCursor;
  
    FETCH NEXT FROM MyCursor INTO @BillingDealerBatchRosterID;

    WHILE @@fetch_status = 0
    BEGIN
        -- START
        SELECT
            COUNT(*) AS ItemTotalCount,
            bdbr.BillingDealerBatchRosterID,
            di.DealerName, 
            -- di.DealerID
            da.ServiceLevelID,
            da.FreshItemFeeID,
            da.WebsiteFeeID,
            da.WebsiteBillingFrequencyID,
            bdbr.BillingDateTo, 
            bdinr.BillingDealerInvoiceNumber
        FROM
            dbo.billing_dealer_batch_item bdbi
        LEFT JOIN   
            dbo.dealer_info di ON di.DealerID = bdbi.DealerID
        LEFT JOIN   
            dbo.dealer_account da ON da.DealerID = di.DealerID
        LEFT JOIN   
            dbo.billing_dealer_batch_roster bdbr ON bdbr.BillingDealerBatchRosterID = bdbi.BillingDealerBatchRosterID
        INNER JOIN  
            dbo.billing_dealer_invoice_number_roster bdinr ON bdinr.DealerID = di.DealerID
        -- LEFT JOIN dbo.lookup__rate_weekly_fee_LR lrwf ON lrwf.ItemQuantity = ItemTotalCount
        -- LEFT OUTER JOIN dbo.billing_dealer_batch bdb ON bdb.DealerID = di.DealerID
        WHERE           
            bdbi.DealerID IN (SELECT DISTINCT DealerID
                              FROM dbo.billing_dealer_batch
                              WHERE DealerID = bdbi.DealerID
                                AND BillingDealerBatchRosterID = @BillingDealerBatchRosterID
                                AND DealerAccountStatusID = 4   -- Dealer Status 4 is ACTIVE only
                             )
            AND bdbi.BillingDealerBatchRosterID = @BillingDealerBatchRosterID -- Roster Week
            AND bdbi.ItemConditionID < 2    -- Less than 2 is AS-IS and Used
        GROUP BY        
            bdinr.BillingDealerInvoiceNumber,
            bdbi.DealerID,
            di.DealerName,
            --   di.DealerID
            bdbr.BillingDateTo,
            da.ServiceLevelID,
            da.FreshItemFeeID,
            da.WebsiteFeeID,
            da.WebsiteBillingFrequencyID,
            bdbr.BillingDealerBatchRosterID
        ORDER BY        
            bdinr.BillingDealerInvoiceNumber;

        -- END 

        FETCH NEXT FROM MyCursor INTO @BillingDealerBatchRosterID;
    END;

CLOSE MyCursor;
DEALLOCATE MyCursor;

As mentioned above, the resulting data is exactly what I need but the unexpected result I am getting is the 4 sections.

Any help would be GREATLY appreciated!

CodePudding user response：

Get rid of the cursor and the loop.

Then change this

    AND bdbi.BillingDealerBatchRosterID = @BillingDealerBatchRosterID

To this

    AND bdbi.BillingDealerBatchRosterID IN (
        SELECT DISTINCT BillingDealerBatchRosterID
        FROM dbo.billing_dealer_batch_roster
        WHERE BillingDealerPeriodID = @varBillingDealerPeriodID
    )

CodePudding user response：

By using the cursor you can just use the following codes. Here, you may need to change the data type for the temp table as per your need.

CREATE PROCEDURE [dbo].[billing_generate_invoice]
    @varBillingDealerPeriodID int
AS
BEGIN

IF OBJECT_ID('tempdb..##tmp_billing_generate_invoice', 'U') IS NOT NULL
BEGIN
    DROP TABLE ##tmp_billing_generate_invoice
END

CREATE TABLE ##tmp_billing_generate_invoice
(
   ItemTotalCount INT,
   BillingDealerBatchRosterID INT,
   DealerName VARCHAR(500),
   ServiceLevelID INT,
   FreshItemFeeID INT,
   WebsiteFeeID INT,
   WebsiteBillingFrequencyID INT,
   BillingDateTo DATE,
   BillingDealerInvoiceNumber VARCHAR(500)
)
    DECLARE @BillingDealerBatchRosterID int;
  
    DECLARE MyCursor CURSOR LOCAL FOR
        SELECT BillingDealerBatchRosterID
        FROM dbo.billing_dealer_batch_roster
        WHERE BillingDealerPeriodID = @varBillingDealerPeriodID;

    OPEN MyCursor;
  
    FETCH NEXT FROM MyCursor INTO @BillingDealerBatchRosterID;

    WHILE @@fetch_status = 0
    BEGIN
        -- START
        INSERT INTO ##tmp_billing_generate_invoice(
                                 ItemTotalCount,
                                 BillingDealerBatchRosterID,
                                 DealerName,
                                 ServiceLevelID,
                                 FreshItemFeeID,
                                 WebsiteFeeID,
                                 WebsiteBillingFrequencyID,
                                 BillingDateTo,
                                 BillingDealerInvoiceNumber
                         )
                    SELECT
                        COUNT(*) AS ItemTotalCount,
                        bdbr.BillingDealerBatchRosterID,
                        di.DealerName, 
                        -- di.DealerID
                        da.ServiceLevelID,
                        da.FreshItemFeeID,
                        da.WebsiteFeeID,
                        da.WebsiteBillingFrequencyID,
                        bdbr.BillingDateTo, 
                        bdinr.BillingDealerInvoiceNumber
                    FROM
                        dbo.billing_dealer_batch_item bdbi
                    LEFT JOIN   
                        dbo.dealer_info di ON di.DealerID = bdbi.DealerID
                    LEFT JOIN   
                        dbo.dealer_account da ON da.DealerID = di.DealerID
                    LEFT JOIN   
                        dbo.billing_dealer_batch_roster bdbr ON bdbr.BillingDealerBatchRosterID = bdbi.BillingDealerBatchRosterID
                    INNER JOIN  
                        dbo.billing_dealer_invoice_number_roster bdinr ON bdinr.DealerID = di.DealerID
                    -- LEFT JOIN dbo.lookup__rate_weekly_fee_LR lrwf ON lrwf.ItemQuantity = ItemTotalCount
                    -- LEFT OUTER JOIN dbo.billing_dealer_batch bdb ON bdb.DealerID = di.DealerID
                    WHERE           
                        bdbi.DealerID IN (SELECT DISTINCT DealerID
                                          FROM dbo.billing_dealer_batch
                                          WHERE DealerID = bdbi.DealerID
                                            AND BillingDealerBatchRosterID = @BillingDealerBatchRosterID
                                            AND DealerAccountStatusID = 4   -- Dealer Status 4 is ACTIVE only
                                         )
                        AND bdbi.BillingDealerBatchRosterID = @BillingDealerBatchRosterID -- Roster Week
                        AND bdbi.ItemConditionID < 2    -- Less than 2 is AS-IS and Used
                    GROUP BY        
                        bdinr.BillingDealerInvoiceNumber,
                        bdbi.DealerID,
                        di.DealerName,
                        --   di.DealerID
                        bdbr.BillingDateTo,
                        da.ServiceLevelID,
                        da.FreshItemFeeID,
                        da.WebsiteFeeID,
                        da.WebsiteBillingFrequencyID,
                        bdbr.BillingDealerBatchRosterID
                    ORDER BY        
                        bdinr.BillingDealerInvoiceNumber;

        -- END 

        FETCH NEXT FROM MyCursor INTO @BillingDealerBatchRosterID;
    END;

CLOSE MyCursor;
DEALLOCATE MyCursor;

SELECT * FROM ##tmp_billing_generate_invoice

END