Dropping #temptables

while recently reviewing a SP, i saw at the end of the SP they are dropping #temptables. Though this code is unncessary, is it worth removing the code? considering that those tables will hold 2-5 million rows, will i be able to get any performance gain???

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

Not strictly necessary, and probably no performance gain.

But one of the golden rules in programming is still: always clean up after yourself. It makes you pay attention to what you're declaring and creating, and it forces you to keep track of things.

Yes it is a good practice to drop the temp tables at the end of the proc. As Satya mentioned saves tempdb space (maintenance of Tempdb if you have any indexes on the tables) etc. It makes more sense if you have so much data in the temp tables.

***********************
Dinakar Nethi
***********************
http://weblogs.sqlteam.com/dinakar/

Hi,

Dont the #temptables dropped automatically after the SP completes?

Here is the SP that is for 4.5 hrs


CREATE PROCEDURE dbo.CalCP
(
@pRowCnt INT OUT,
@pPrintSQL BIT = 0,
@pExecuteSQL BIT = 1
)

AS


SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET XACT_ABORT OFF
SET DATEFORMAT YMD

SET DATEFIRST 7 -- 7 (default, U.S. English) Sunday

DECLARE @lvcSPName SYSNAME -- For Error Handling
DECLARE @lvcStepName VARCHAR(200) -- For Error Handling
DECLARE @lvcErrorCode VARCHAR(7) -- For Error Handling
DECLARE @lvcTableName VARCHAR(100) -- For Holding The Table Name
DECLARE @lnError INTEGER -- For Holding Error Number

DECLARE @vRowCnt INT
DECLARE @vReturnCode INT -- For holding Return Code
DECLARE @vErrorCode INT
DECLARE @vRetCode INT

DECLARE @vDatabaseName SYSNAME
DECLARE @vTableName SYSNAME
DECLARE @vIndexName SYSNAME

DECLARE @vDefaultSupplierID INT
DECLARE @vDefaultSupplierSeriesNumber INT

DECLARE @vTempPSDyValidCPSupplierID INT
DECLARE @vTempPSDyNoValidCPSupplierID INT


DECLARE @vCreateIndexStatement VARCHAR (2000)

DECLARE @vTempTableName1 SYSNAME
DECLARE @vTempIndexName1 SYSNAME
DECLARE @vTempTableName2 SYSNAME
DECLARE @vTempIndexName2 SYSNAME
DECLARE @vTempTableName3 SYSNAME
DECLARE @vTempIndexName3 SYSNAME

BEGIN

SET @vReturnCode = 0
SET @vRetCode = 0
SET @pRowCnt = 0
SET @lvcSPName = OBJECT_NAME(@@PROCID)

SET @vDatabaseName = 'dmETL'
SET @vTableName = 'SPSDYCostPrice'
SET @vIndexName = @vTableName + '_CL_IX'

SET @vTempTableName1 = '#TempPSDyValidCPSupplier'
SET @vTempIndexName1 = @vTempTableName1 + '_CL_IX'


SET @vTempTableName2 = '#TempPSDyNoValidCPSupplier'
SET @vTempIndexName2 = @vTempTableName2 + '_CL_IX'


SET @vTempTableName3 = '#TempPSDyValidNoValidCPSupplier'
SET @vTempIndexName3 = @vTempTableName3 + '_CL_IX'


SET @vTempPSDyNoValidCPSupplierID = 0
SET @vTempPSDyValidCPSupplierID = 0

EXEC @vRetCode = dbo.csp_DropTable @vDatabaseName, @vTableName, @pPrintSQL, @pExecuteSQL

IF @vRetCode <> 0
BEGIN
SET @lvcTableName = 'csp_DropTable'
SET @lvcErrorCode = 'DMW3420'
SET @vReturnCode = -1
SET @lvcStepName = 'SP: ' + @lvcTableName
GOTO OnError
END

SELECT @vDefaultSupplierID = SupplierID,
@vDefaultSupplierSeriesNumber = SupplierSeriesNumber
FROM dbo.SPSDYSupplier (NOLOCK)
WHERE SupplierLongName = 'DEFAULT'

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0 OR @vRowCnt = 0

BEGIN
SET @lvcTableName = 'SPSDYSupplier'
SET @lvcErrorCode = 'DMW3421'
SET @vReturnCode = -1
SET @lvcStepName = 'Select from ' + @lvcTableName
GOTO OnError
END

SELECT ProductID, ProductSCDID, DeliveryMethodID
INTO #TempProduct
FROM dmBTK.dbo.Product (NOLOCK)

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0 OR @vRowCnt = 0

BEGIN
SET @lvcTableName = '#TempProduct'
SET @lvcErrorCode = 'DMW3438'
SET @vReturnCode = -1
SET @lvcStepName = 'Select from ' + @lvcTableName
GOTO OnError
END


SET @vCreateIndexStatement = 'CREATE CLUSTERED INDEX #TempProduct_IX ON #TempProduct (ProductID, ProductSCDID)'

IF @pPrintSQL = 1

PRINT @vCreateIndexStatement

IF @pExecuteSQL = 1

BEGIN

EXEC (@vCreateIndexStatement)

SELECT @lnError = @@ERROR

IF @lnError <> 0
BEGIN
SET @lvcTableName = '#TempProduct'
SET @lvcErrorCode = 'DMW3437'
SET @vReturnCode = -1
SET @lvcStepName = 'Create Index #TempProduct_IX on: ' + @lvcTableName
GOTO OnError
END

END


-- Find all Valid or Invalid CP's and a Count of CPs (By Supplier) within the Date Boundaries
-- Note: Change 1 to @vDefaultSupplier
SELECT
LPSD.BusinessDateID,
LPSD.ProductID,
LPSD.StoreID,
LPSD.ProductSCDID,
ISNULL(SPSDCP.SupplierID, @vDefaultSupplierID) AS SupplierID,
CASE WHEN MIN(ISNULL(SPSDCP.StartDateID, 0)) = 0 THEN 0 ELSE COUNT(*) END AS CostPriceCount,
CASE WHEN MIN(ISNULL(SPSDCP.StartDateID, 0)) = 0 THEN 0 ELSE 1 END AS ValidCostPriceFlag
INTO #TempPSDyValidNoValidCPSupplier
FROM dbo.LoadProductStoreDay LPSD (NOLOCK)
LEFT OUTER JOIN dmBTK.dbo.SupplierProductStoreDayCostPrice SPSDCP (NOLOCK)
ON (SPSDCP.ProductID = LPSD.ProductID
AND SPSDCP.StoreID = LPSD.StoreID
--AND SPSDCP.ProductSCDID = LPSD.ProductSCDID -- Live Fix QC: 1152
AND LPSD.BusinessDateID >= SPSDCP.StartDateID
AND LPSD.BusinessDateID <= ISNULL(SPSDCP.EndDateID, 99991231))
--OR (SPSDCP.StartDateID >= LPSD.BusinessDateID AND SPSDCP.StartDateID <= LPSD.BusinessDateID)) -- Not needed
GROUP BY LPSD.BusinessDateID,
LPSD.ProductID,
LPSD.StoreID,
LPSD.ProductSCDID,
ISNULL(SPSDCP.SupplierID, @vDefaultSupplierID)

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = '#TempPSDyValidNoValidCPSupplier'
SET @lvcErrorCode = 'DMW3422'
SET @vReturnCode = -1
SET @lvcStepName = 'Select into ' + @lvcTableName
GOTO OnError
END


SET @vCreateIndexStatement = 'CREATE CLUSTERED INDEX ' + @vTempIndexName3 + ' ON ' + @vTempTableName3 + ' (BusinessDateID, ProductID, StoreID, SupplierID) '

IF @pPrintSQL = 1

PRINT @vCreateIndexStatement

IF @pExecuteSQL = 1

BEGIN

EXEC (@vCreateIndexStatement)

SELECT @lnError = @@ERROR

IF @lnError <> 0
BEGIN
SET @lvcTableName = @vTempTableName3
SET @lvcErrorCode = 'DMW3437'
SET @vReturnCode = -1
SET @lvcStepName = 'Create Index: ' + @vTempIndexName3 + ' on: ' + @lvcTableName
GOTO OnError
END

END

-- For All CP's with Multiple Supplier CP's
SELECT CAST(NULL AS INT) AS TempPSDyValidCPSupplierID,
A.BusinessDateID,
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.SupplierID,
S.SupplierSeriesNumber,
S.StartDateID AS SupplierSCDStartDateID, -- Added 12.01.2007
S.EndDateID AS SupplierSCDEndDateID, -- Added 12.01.2007
CASE WHEN SPSDCP.CalculatedWeightCostPriceFlag = 0 THEN SPSDCP.UnitCostPriceMajor ELSE SPSDCP.WeightCostPriceMajor END AS CostPriceMajor,
SPSDCP.UnitDutyChargeMajor AS DutyChargeMajor,
SPSDCP.StartDateID AS StartDateID,
ISNULL(SPSDCP.EndDateID, 99991231) AS EndDateID,
SPSDCP.CalculatedWeightCostPriceFlag,
1 AS SupplierCount,
A.CostPriceCount,
CAST(0 AS BIT) AS PreviousCostPriceFlag,
LBIPSCD.DeliveryMethodID
INTO #TempPSDyValidCPSupplier
FROM #TempPSDyValidNoValidCPSupplier A (NOLOCK)
INNER JOIN dmBTK.dbo.SupplierProductStoreDayCostPrice SPSDCP (NOLOCK)
ON (A.ProductID = SPSDCP.ProductID
AND A.StoreID = SPSDCP.StoreID
AND A.SupplierID = SPSDCP.SupplierID
AND A.BusinessDateID >= SPSDCP.StartDateID
AND A.BusinessDateID <= ISNULL(SPSDCP.EndDateID, 99991231))
INNER JOIN dbo.SPSDYSupplier S (NOLOCK)
ON (SPSDCP.SupplierID = S.SupplierID
AND A.BusinessDateID >= SPSDCP.StartDateID
AND A.BusinessDateID <= ISNULL(SPSDCP.EndDateID, 99991231))
INNER JOIN #TempProduct LBIPSCD (NOLOCK) -- Use Data Mart data in a #Temp Table NOT the DCS Load Tables
ON (LBIPSCD.ProductSCDID = A.ProductSCDID
AND LBIPSCD.ProductID = A.ProductID)

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = @vTempTableName1
SET @lvcErrorCode = 'DMW3423'
SET @vReturnCode = -1
SET @lvcStepName = 'Select into ' + @lvcTableName
GOTO OnError
END

SET @vCreateIndexStatement = 'CREATE CLUSTERED INDEX ' + @vTempIndexName1 + ' ON ' + @vTempTableName1 + ' (BusinessDateID, ProductID, StoreID, SupplierSeriesNumber, SupplierSCDStartDateID DESC, StartDateID DESC, CostPriceMajor DESC, SupplierID DESC, ProductSCDID DESC) '

IF @pPrintSQL = 1

PRINT @vCreateIndexStatement

IF @pExecuteSQL = 1

BEGIN

EXEC (@vCreateIndexStatement)

SELECT @lnError = @@ERROR

IF @lnError <> 0
BEGIN
SET @lvcTableName = @vTempTableName1
SET @lvcErrorCode = 'DMW3424'
SET @vReturnCode = -1
SET @lvcStepName = 'Create Index: ' + @vTempIndexName1 + ' on: ' + @lvcTableName
GOTO OnError
END

END

UPDATE #TempPSDyValidCPSupplier
SET @vTempPSDyValidCPSupplierID = TempPSDyValidCPSupplierID = @vTempPSDyValidCPSupplierID + 1

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = @vTempTableName1
SET @lvcErrorCode = 'DMW3425'
SET @vReturnCode = -1
SET @lvcStepName = 'Update of ' + @lvcTableName
GOTO OnError
END

-- For All CP's with Multiple Supplier CP's, find the Single 'Most Valid CP' Record
SELECT
A.TempPSDyValidCPSupplierID,
A.BusinessDateID,
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.SupplierID,
A.SupplierSeriesNumber,
A.CostPriceMajor,
A.DutyChargeMajor,
A.StartDateID,
A.EndDateID,
A.CalculatedWeightCostPriceFlag,
A.SupplierCount,
A.CostPriceCount,
A.PreviousCostPriceFlag,
A.DeliveryMethodID
INTO #TempPSDyValidCPSupplierSingleCP
FROM #TempPSDyValidCPSupplier A (NOLOCK)
INNER JOIN
(
SELECT
MIN(TempPSDyValidCPSupplierID) AS TempPSDyValidCPSupplierID,
BusinessDateID,
ProductID,
StoreID,
SupplierSeriesNumber
FROM #TempPSDyValidCPSupplier (NOLOCK)
GROUP BY BusinessDateID,
ProductID,
StoreID,
SupplierSeriesNumber
) AS B
ON (A.BusinessDateID = B.BusinessDateID
AND A.ProductID = B.ProductID
AND A.StoreID = B.StoreID
AND A.SupplierSeriesNumber = B.SupplierSeriesNumber
AND A.TempPSDyValidCPSupplierID = B.TempPSDyValidCPSupplierID)

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = '#TempPSDyValidCPSupplierSingleCP'
SET @lvcErrorCode = 'DMW3426'
SET @vReturnCode = -1
SET @lvcStepName = 'Select into ' + @lvcTableName
GOTO OnError
END

-- Obtain Delivery Info for these no Valid Cost Prices
SELECT
X.BusinessDateID,
X.ProductID,
X.StoreID,
X.ProductSCDID,
LBIPSCD.DeliveryMethodID,
B.StartDateID,
B.EndDateID,
ISNULL(B.DepotID, -1) AS DepotID -- Default Depot
INTO #TempPSDyNoValidCPDelivery
FROM #TempPSDyValidNoValidCPSupplier X
--INNER JOIN dbo.LoadBIProductSCD LBIPSCD (NOLOCK) -- Live Performance Fix use Data Mart
INNER JOIN #TempProduct LBIPSCD (NOLOCK) -- Use Data Mart data in a #Temp Table NOT the DCS Load Tables
ON (LBIPSCD.ProductSCDID = X.ProductSCDID
AND LBIPSCD.ProductID = X.ProductID)
LEFT OUTER JOIN dbo.SPSDYStoreDepotDeliveryMethod B (NOLOCK)
ON (B.StoreID = X.StoreID
AND B.DeliveryMethodID = LBIPSCD.DeliveryMethodID
AND X.BusinessDateID >= B.StartDateID
AND X.BusinessDateID <= ISNULL(B.EndDateID, 99991231))
--OR (B.StartDateID >= X.BusinessDateID AND B.StartDateID <= X.BusinessDateID)) -- Not Needed
WHERE X.ValidCostPriceFlag = 0

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = '#TempPSDyNoValidCPDelivery'
SET @lvcErrorCode = 'DMW3427'
SET @vReturnCode = -1
SET @lvcStepName = 'Select into ' + @lvcTableName
GOTO OnError
END

-- Find all Records where there have been a Delivery from 1 or more Suppliers (the number of Suppliers is irrelevant) these will be set to the Default Supplier
SELECT
A.BusinessDateID,
A.ProductID,
A.ProductSCDID,
A.StoreID,
A.DepotID,
A.DeliveryMethodID
INTO dbo.#TempPSDyNoValidCPDefaultSupplier
FROM dbo.#TempPSDyNoValidCPDelivery A (NOLOCK) -- What Depot is used to deliver a Product to a Store for a BusinessDate
INNER JOIN dbo.SPSDYSupplierProductDepotDelivery B (NOLOCK) --List of all Deliveries from a Supplier to a Depot
ON (A.ProductID = B.ProductID
AND A.DepotID = B.DepotID
AND B.DeliveryDateID < A.BusinessDateID)
GROUP BY A.BusinessDateID, A.ProductID, A.ProductSCDID, A.StoreID, A.DepotID, A.DeliveryMethodID

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = '#TempPSDyNoValidCPDefaultSupplier'
SET @lvcErrorCode = 'DMW3428'
SET @vReturnCode = -1
SET @lvcStepName = 'Select into ' + @lvcTableName
GOTO OnError
END

-- Find all No Delivery Records where there could have been a possible previous Cost Price
SELECT
A.BusinessDateID,
A.ProductID,
A.ProductSCDID,
A.StoreID,
A.DepotID,
A.DeliveryMethodID
INTO dbo.#TempPSDyNoValidCPPossibleMostRecentCP
FROM dbo.#TempPSDyNoValidCPDelivery A (NOLOCK)
LEFT OUTER JOIN dbo.#TempPSDyNoValidCPDefaultSupplier B (NOLOCK)
ON (A.BusinessDateID = B.BusinessDateID
AND A.ProductID = B.ProductID
AND A.StoreID = B.StoreID
AND A.DepotID = B.DepotID
AND A.DeliveryMethodID = B.DeliveryMethodID)
WHERE B.BusinessDateID IS NULL

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = '#TempPSDyNoValidCPPossibleMostRecentCP'
SET @lvcErrorCode = 'DMW3429'
SET @vReturnCode = -1
SET @lvcStepName = 'Select into ' + @lvcTableName
GOTO OnError
END

-- Find all Records where there are previous valid Cost Prices but there have not been any Deliveries
SELECT
X.ProductID,
X.ProductSCDID,
X.StoreID,
X.SupplierID,
S.SupplierSeriesNumber,
S.StartDateID AS SupplierSCDStartDateID,
S.EndDateID AS SupplierSCDEndDateID,
X.CostPriceMajor,
X.DutyChargeMajor,
X.MinBusinessDateID,
X.MaxBusinessDateID,
X.CalculatedWeightCostPriceFlag,
1 AS SupplierCount,
1 AS PreviousCostPriceFlag,
X.DeliveryMethodID,
X.DepotID
INTO dbo.#TempPSDyNoValidCPMostRecentCP
FROM
(
SELECT -- Without ProductSCDID
B.ProductID,
A.ProductSCDID,
B.StoreID,
B.SupplierID,
CASE WHEN B.CalculatedWeightCostPriceFlag = 0 THEN B.UnitCostPriceMajor ELSE B.WeightCostPriceMajor END AS CostPriceMajor,
B.UnitDutyChargeMajor AS DutyChargeMajor,
A.MinBusinessDateID,
A.MaxBusinessDateID,
B.CalculatedWeightCostPriceFlag,
A.DeliveryMethodID,
A.DepotID
FROM dmBTK.dbo.SupplierProductStoreDayCostPrice B (NOLOCK)
INNER JOIN
(
SELECT
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.DeliveryMethodID,
A.DepotID,
MAX(B.StartDateID) AS PreviousStartDateID,
MAX(ISNULL(B.EndDateID, 99991231)) AS PreviousEndDateID,
MIN(A.BusinessDateID) AS MinBusinessDateID,
MAX(A.BusinessDateID) AS MaxBusinessDateID
FROM #TempPSDyNoValidCPPossibleMostRecentCP A (NOLOCK)
INNER JOIN dmBTK.dbo.SupplierProductStoreDayCostPrice B (NOLOCK)
ON B.ProductID = A.ProductID
AND B.StoreID = A.StoreID
AND B.StartDateID <= A.BusinessDateID
AND B.EndDateID <= A.BusinessDateID
GROUP BY A.ProductID, A.StoreID, A.ProductSCDID, A.DeliveryMethodID, A.DepotID
) AS A
ON A.ProductID = B.ProductID
AND A.StoreID = B.StoreID
AND A.PreviousStartDateID = B.StartDateID
AND A.PreviousEndDateID = ISNULL(B.EndDateID, 99991231)

UNION ALL

SELECT -- With ProductSCDID
B.ProductID,
A.ProductSCDID,
B.StoreID,
B.SupplierID,
CASE WHEN B.CalculatedWeightCostPriceFlag = 0 THEN B.UnitCostPriceMajor ELSE B.WeightCostPriceMajor END AS CostPriceMajor,
B.UnitDutyChargeMajor AS DutyChargeMajor,
A.MinBusinessDateID,
A.MaxBusinessDateID,
B.CalculatedWeightCostPriceFlag,
A.DeliveryMethodID,
A.DepotID
FROM dmBTK.dbo.SupplierProductStoreDayCostPrice B (NOLOCK)
INNER JOIN
(
SELECT
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.DeliveryMethodID,
A.DepotID,
MAX(B.StartDateID) AS PreviousStartDateID,
MAX(ISNULL(B.EndDateID, 99991231)) AS PreviousEndDateID,
MIN(A.BusinessDateID) AS MinBusinessDateID,
MAX(A.BusinessDateID) AS MaxBusinessDateID
FROM #TempPSDyNoValidCPPossibleMostRecentCP A (NOLOCK)
INNER JOIN dmBTK.dbo.SupplierProductStoreDayCostPrice B (NOLOCK)
ON B.ProductID = A.ProductID
AND B.StoreID = A.StoreID
AND B.ProductSCDID = A.ProductSCDID
AND B.StartDateID <= A.BusinessDateID
AND B.EndDateID <= A.BusinessDateID
GROUP BY A.ProductID, A.StoreID, A.ProductSCDID, A.DeliveryMethodID, A.DepotID
) AS A
ON A.ProductID = B.ProductID
AND A.StoreID = B.StoreID
AND A.ProductSCDID = B.ProductSCDID
AND A.PreviousStartDateID = B.StartDateID
AND A.PreviousEndDateID = ISNULL(B.EndDateID, 99991231)
) AS X
INNER JOIN dbo.SPSDYSupplier S (NOLOCK)
ON (S.SupplierID = X.SupplierID)

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = '#TempPSDyNoValidCPMostRecentCP'
SET @lvcErrorCode = 'DMW3430'
SET @vReturnCode = -1
SET @lvcStepName = 'Select into ' + @lvcTableName
GOTO OnError
END

-- Find All Most Recent Cost Prices (could have multiple records)
SELECT CAST(NULL AS INT) AS TempPSDyNoValidCPSupplierID,
LPSD.BusinessDateID,
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.SupplierID,
A.SupplierSeriesNumber,
A.SupplierSCDStartDateID,
A.SupplierSCDEndDateID,
A.CostPriceMajor,
A.DutyChargeMajor,
A.MinBusinessDateID AS StartDateID,
A.MaxBusinessDateID AS EndDateID,
A.CalculatedWeightCostPriceFlag,
1 AS SupplierCount,
COUNT(*) AS CostPriceCount,
CAST(1 AS BIT) AS PreviousCostPriceFlag,
A.DeliveryMethodID
INTO #TempPSDyNoValidCPSupplier
FROM #TempPSDyNoValidCPMostRecentCP A (NOLOCK)
INNER JOIN dbo.LoadProductStoreDay LPSD (NOLOCK)
ON A.ProductID = LPSD.ProductID
AND A.StoreID = LPSD.StoreID
AND A.ProductSCDID = LPSD.ProductSCDID
AND A.MinBusinessDateID <= LPSD.BusinessDateID
AND A.MaxBusinessDateID >= LPSD.BusinessDateID
GROUP BY LPSD.BusinessDateID,
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.SupplierID,
A.SupplierSeriesNumber,
A.SupplierSCDStartDateID,
A.SupplierSCDEndDateID,
A.CostPriceMajor,
A.DutyChargeMajor,
A.MinBusinessDateID,
A.MaxBusinessDateID,
A.CalculatedWeightCostPriceFlag,
A.DeliveryMethodID

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = '#TempPSDyNoValidCPSupplier'
SET @lvcErrorCode = 'DMW3431'
SET @vReturnCode = -1
SET @lvcStepName = 'Select into ' + @lvcTableName
GOTO OnError
END

SET @vCreateIndexStatement = 'CREATE CLUSTERED INDEX ' + @vTempIndexName2 + ' ON ' + @vTempTableName2 + ' (BusinessDateID, ProductID, StoreID, SupplierSeriesNumber, SupplierSCDStartDateID DESC, StartDateID DESC, CostPriceMajor DESC, SupplierID DESC, ProductSCDID) '


IF @pPrintSQL = 1

PRINT @vCreateIndexStatement

IF @pExecuteSQL = 1

BEGIN

EXEC (@vCreateIndexStatement)

SELECT @lnError = @@ERROR

IF @lnError <> 0
BEGIN
SET @lvcTableName = @vTempTableName2
SET @lvcErrorCode = 'DMW3432'
SET @vReturnCode = -1
SET @lvcStepName = 'Create Index: ' + @vTempIndexName2 + ' on: ' + @lvcTableName
GOTO OnError
END

END

UPDATE #TempPSDyNoValidCPSupplier
SET @vTempPSDyNoValidCPSupplierID = TempPSDyNoValidCPSupplierID = @vTempPSDyNoValidCPSupplierID + 1

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = @vTempTableName2
SET @lvcErrorCode = 'DMW3433'
SET @vReturnCode = -1
SET @lvcStepName = 'Update of ' + @lvcTableName
GOTO OnError
END

-- For All Most Recent CP's, find the Single 'Most Valid CP' Record
SELECT
A.TempPSDyNoValidCPSupplierID,
A.BusinessDateID,
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.SupplierID,
A.SupplierSeriesNumber,
A.CostPriceMajor,
A.DutyChargeMajor,
A.StartDateID,
A.EndDateID,
A.CalculatedWeightCostPriceFlag,
A.SupplierCount,
A.CostPriceCount,
A.PreviousCostPriceFlag,
A.DeliveryMethodID
INTO #TempPSDyNoValidCPSupplierSingleCP
FROM #TempPSDyNoValidCPSupplier A (NOLOCK)
INNER JOIN
(
SELECT
MIN(TempPSDyNoValidCPSupplierID) AS TempPSDyNoValidCPSupplierID,
BusinessDateID,
ProductID,
StoreID, SupplierSeriesNumber
FROM #TempPSDyNoValidCPSupplier (NOLOCK)
GROUP BY BusinessDateID,
ProductID,
StoreID,
SupplierSeriesNumber
) AS B
ON (A.BusinessDateID = B.BusinessDateID
AND A.ProductID = B.ProductID
AND A.StoreID = B.StoreID
AND A.SupplierSeriesNumber = B.SupplierSeriesNumber
AND A.TempPSDyNoValidCPSupplierID = B.TempPSDyNoValidCPSupplierID)

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = '#TempPSDyNoValidCPSupplierSingleCP'
SET @lvcErrorCode = 'DMW3434'
SET @vReturnCode = -1
SET @lvcStepName = 'Select into ' + @lvcTableName
GOTO OnError
END


-- Find all Records with no Previous Cost Price Records or where there have been Deliveries . These will be allocated to the Default Supplier
SELECT
A.BusinessDateID,
A.ProductID,
A.ProductSCDID,
A.StoreID,
@vDefaultSupplierID AS SupplierID,
@vDefaultSupplierSeriesNumber AS SupplierSeriesNumber,
A.StartDateID,
A.EndDateID,
CAST(NULL AS SMALLMONEY) AS CostPriceMajor,
CAST(NULL AS SMALLMONEY) AS DutyChargeMajor,
CAST(0 AS BIT) AS CalculatedWeightCostPriceFlag,
1 AS SupplierCount,
0 AS CostPriceCount,
CAST(0 AS BIT) AS PreviousCostPriceFlag,
A.DeliveryMethodID
INTO #TempPSDyNoValidCP
FROM
(
SELECT
A.BusinessDateID,
A.ProductID,
A.ProductSCDID,
A.StoreID,
A.BusinessDateID AS StartDateID,
A.BusinessDateID AS EndDateID,
A.DeliveryMethodID
FROM #TempPSDyNoValidCPPossibleMostRecentCP A (NOLOCK)
LEFT OUTER JOIN #TempPSDyNoValidCPSupplierSingleCP B (NOLOCK)
--LEFT OUTER JOIN #TempPSDyNoValidCPSupplier B (NOLOCK)
ON (A.BusinessDateID = B.BusinessDateID
AND A.ProductID = B.ProductID
AND A.StoreID = B.StoreID
AND A.ProductSCDID = B.ProductSCDID)
WHERE B.BusinessDateID IS NULL

UNION

SELECT
A.BusinessDateID,
A.ProductID,
A.ProductSCDID,
A.StoreID,
A.BusinessDateID AS StartDateID,
A.BusinessDateID AS EndDateID,
A.DeliveryMethodID
FROM #TempPSDyNoValidCPDefaultSupplier A (NOLOCK)
) AS A

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = '#TempPSDyNoValidCP'
SET @lvcErrorCode = 'DMW3435'
SET @vReturnCode = -1
SET @lvcStepName = 'Select into ' + @lvcTableName
GOTO OnError
END

SELECT
IDENTITY(INT, 1, 1) AS SPSDYCostPriceID,
A.BusinessDateID,
A.ProductID,
A.ProductSCDID,
A.StoreID,
A.SupplierID,
A.SupplierSeriesNumber,
A.CostPriceMajor,
A.DutyChargeMajor,
A.StartDateID,
A.EndDateID,
A.CalculatedWeightCostPriceFlag,
A.SupplierCount,
A.CostPriceCount,
A.PreviousCostPriceFlag,
A.DeliveryMethodID
INTO dbo.SPSDYCostPrice
FROM
(
SELECT
BusinessDateID,
ProductID,
ProductSCDID,
StoreID,
SupplierID,
SupplierSeriesNumber,
CostPriceMajor,
DutyChargeMajor,
StartDateID,
EndDateID,
CalculatedWeightCostPriceFlag,
SupplierCount,
CostPriceCount,
PreviousCostPriceFlag,
DeliveryMethodID
--FROM #TempPSDyValidCPSupplier (NOLOCK)
FROM #TempPSDyValidCPSupplierSingleCP (NOLOCK)

UNION ALL

SELECT
BusinessDateID,
ProductID,
ProductSCDID,
StoreID,
SupplierID,
SupplierSeriesNumber,
CostPriceMajor,
DutyChargeMajor,
StartDateID,
EndDateID,
CalculatedWeightCostPriceFlag,
SupplierCount,
CostPriceCount,
PreviousCostPriceFlag,
DeliveryMethodID
--FROM #TempPSDyNoValidCPSupplier (NOLOCK)
FROM #TempPSDyNoValidCPSupplierSingleCP (NOLOCK)

UNION ALL

SELECT
BusinessDateID,
ProductID,
ProductSCDID,
StoreID,
SupplierID,
SupplierSeriesNumber,
CostPriceMajor,
DutyChargeMajor,
StartDateID,
EndDateID,
CalculatedWeightCostPriceFlag,
SupplierCount,
CostPriceCount,
PreviousCostPriceFlag,
DeliveryMethodID
FROM #TempPSDyNoValidCP (NOLOCK)
) AS A

SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR

IF @lnError <> 0

BEGIN
SET @lvcTableName = @vTableName
SET @lvcErrorCode = 'DMW3436'
SET @vReturnCode = -1
SET @lvcStepName = 'Select Into ' + @lvcTableName
GOTO OnError
END

SELECT @pRowCnt = @vRowCnt

GOTO OnSuccess

END

OnError:


BEGIN

SET @vErrorCode = CONVERT(INT, RIGHT(@lvcErrorCode, 4))

RAISERROR (50510, 16, 1, @lvcSPName, @lvcStepName, @vErrorCode)

GOTO OnSuccess

END

OnSuccess:

BEGIN

DROP TABLE #TempPSDyNoValidCP
DROP TABLE #TempPSDyNoValidCPSupplier
DROP TABLE #TempPSDyNoValidCPSupplierSingleCP
DROP TABLE #TempPSDyNoValidCPMostRecentCP
DROP TABLE #TempPSDyNoValidCPPossibleMostRecentCP
DROP TABLE #TempPSDyNoValidCPDefaultSupplier
DROP TABLE #TempPSDyNoValidCPDelivery
DROP TABLE #TempPSDyValidNoValidCPSupplier
DROP TABLE #TempPSDyValidCPSupplier
DROP TABLE #TempPSDyValidCPSupplierSingleCP
DROP TABLE #TempProduct

RETURN (@vReturnCode)

END





Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

We don't have to look at the actual procedure, it's considered a best practice to clean up after yourself.

quote:Originally posted by Adriaan

We don't have to look at the actual procedure, it's considered a best practice to clean up after yourself.

I have pasted the proc as Satya asked to check for tuning possiblities.

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."