我正在嘗試從存盤程序中獲取串列，但我相信我可能使用了錯誤的方法？
我得到的資料很好，但是將結果分成幾個部分而不是一個連續的結果。
我需要它是一個連續的結果，因為它需要然后匯出到一個會計程式。
更新
我可能應該提到我正在自學 SQL 和程式，所以我不完全知道我在做什么，所以請原諒我。:)
我上周大部分時間都在寫作和重寫，然后才終于來這里尋求幫助。
我現在意識到我寫的內容過于復雜（更不用說沒有按照我需要的方式給出回應。）我試圖重新利用我在其他地方找到的代碼。
這是我更正的代碼。
@varBillingDealerPeriodID int
AS
DECLARE @BillingDealerBatchRosterID int;
BEGIN TRY
SELECT count( * ) AS ItemTotalCount
, di.DealerName
, di.DealerID
, 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.billing_dealer_batch_roster bdbr ON bdbr.BillingDealerBatchRosterID = bdbi.BillingDealerBatchRosterID
LEFT JOIN dbo.billing_dealer_invoice_number_roster bdinr ON bdinr.DealerID = di.DealerID
WHERE bdbi.BillingDealerBatchRosterID IN (
SELECT DISTINCT BillingDealerBatchRosterID
FROM dbo.billing_dealer_batch_roster
WHERE BillingDealerPeriodID = @varBillingDealerPeriodID
)
AND bdbi.ItemConditionID < 2
GROUP BY di.DealerName
, di.DealerID
, bdbr.BillingDateTo
, bdinr.BillingDealerInvoiceNumber
END TRY
謝謝大家的幫助！
uj5u.com熱心網友回復：
擺脫游標和回圈。
然后改變這個
AND bdbi.BillingDealerBatchRosterID = @BillingDealerBatchRosterID
對此
AND bdbi.BillingDealerBatchRosterID IN (
SELECT DISTINCT BillingDealerBatchRosterID
FROM dbo.billing_dealer_batch_roster
WHERE BillingDealerPeriodID = @varBillingDealerPeriodID
)
uj5u.com熱心網友回復：
通過使用游標，您可以使用以下代碼。在這里，您可能需要根據需要更改臨時表的資料型別。
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/413619.html
標籤：