Using Temporary Table, SQL Server 2005

ALTER PROCEDURE dbo.BUDGET_COMMITMENT_QTY_REPORT

(

@BudgetYear NVARCHAR(50) = null,

@BudgetVersion NVARCHAR(50) = null,

@Company NVARCHAR(50) = null,

@Division NVARCHAR(50) = null,

@CostCenter NVARCHAR(50) = null,

@SubAccount NVARCHAR(50) = null,

@SalesDivision NVARCHAR(50) = null,

@AsOnDate DATETIME = null

)

AS

SET NOCOUNT ON

BEGIN

DECLARE @Select NVARCHAR(4000)

DECLARE @Param NVARCHAR(4000)

SET @Param = ' @BudgetYear NVARCHAR(50) = null,

@BudgetVersion NVARCHAR(50) = null,

@Company NVARCHAR(50) = null,

@Division NVARCHAR(50) = null,

@CostCenter NVARCHAR(50) = null,

@SubAccount NVARCHAR(50) = null,

@SalesDivision NVARCHAR(50) = null,

@AsOnDate DATETIME = null '

CREATE TABLE #TEMPTABLE

(

BudgetType NVARCHAR(50), BudgetYear NVARCHAR(50), Company NVARCHAR(50), Division NVARCHAR(50), SalesDivision NVARCHAR(50), Costcenter NVARCHAR(50), SubAccount NVARCHAR(50), Display NVARCHAR(50), Amount DECIMAL(29,2), Quantity DECIMAL(29,2), RQDate DateTime

)

INSERT INTO #TEMPTABLE

SELECT BUDGET_TYPE AS BudgetType, BUDGET_YEAR AS year, COMPANY_CODE AS Company, DIVISION_CODE AS Division,

SALES_DIVISION AS SalesDivision, COST_CENTER AS costCenter, SUB_ACCOUNT_CODE AS subAccount, 'Precommitment' AS Display, SUM(AMOUNT)

AS TotalValue, SUM(QUANTITY) AS Quantity, PR_REQ_DATE AS RQDate

FROM ECMS_PR_BUDGET_CHECK

GROUP BY BUDGET_TYPE, BUDGET_YEAR, COMPANY_CODE, SALES_DIVISION, COST_CENTER, SUB_ACCOUNT_CODE, QUANTITY, DIVISION_CODE,

PR_REQ_DATE

UNION ALL

SELECT BUDGET_TYPE AS BudgetType, BUDGET_YEAR AS BudgetYear, COMPANY_CODE AS Company, DIVISION_CODE AS Division,

SALES_DIVISION AS SalesDivision, COST_CENTER AS CostCenter, SUB_ACCOUNT_CODE AS SubAccount, 'Commitment' AS Display, SUM(AMOUNT)

AS TotalValue, SUM(QUANTITY) AS Quantity, PO_REQ_DATE AS RQDate

FROM ECMS_PO_BUDGET_CHECK

GROUP BY BUDGET_TYPE, BUDGET_YEAR, COMPANY_CODE, SALES_DIVISION, COST_CENTER, SUB_ACCOUNT_CODE, DIVISION_CODE, PO_REQ_DATE

--SELECT * FROM #TEMPTABLE

SET @Select = 'SELECT #TEMPTABLE.BudgetYear, #TEMPTABLE.Display, #TEMPTABLE.Amount,

#TEMPTABLE.Quantity, ECMS_MST_COMPANY.Company_Name, ECMS_MST_COST_CENTER.Cost_center_desc,

ECMS_MST_SUB_ACCOUNT.SUB_AC_NAME, ECMS_MST_SALES_DIVISION.SALES_DIVISION_NAME , ECMS_MST_DIVISION.DIVISION_NAME

FROM #TEMPTABLE

INNER JOIN

ECMS_MST_BUDGET_TYPE ON #TEMPTABLE.BudgetType = ECMS_MST_BUDGET_TYPE.BUDGET_CODE INNER JOIN

ECMS_MST_COMPANY ON #TEMPTABLE.Company = ECMS_MST_COMPANY.Company_Code INNER JOIN

ECMS_MST_COST_CENTER ON #TEMPTABLE.Costcenter = ECMS_MST_COST_CENTER.Cost_center_code INNER JOIN

ECMS_MST_SUB_ACCOUNT ON #TEMPTABLE.SubAccount = ECMS_MST_SUB_ACCOUNT.SUB_AC_CODE INNER JOIN

ECMS_MST_SALES_DIVISION ON #TEMPTABLE.SalesDivision = ECMS_MST_SALES_DIVISION.SALES_DIVISION_CODE INNER JOIN

ECMS_MST_DIVISION ON #TEMPTABLE.Division = ECMS_MST_DIVISION.DIVISION_CODE '

IF @BudgetYear<>0 AND @BudgetYear IS NOT NULL

SET @Select = @Select+ ' AND (BudgetYear=@BudgetYear) '

IF @BudgetYear IS NOT NULL

SET @Select = @Select+ ' AND (BudgetType=@BudgetVersion) '

IF @Company<>0 AND @Company IS NOT NULL

SET @Select = @Select+ ' AND (Company=@Company) '

IF @Division<>0 AND @Division IS NOT NULL

SET @Select = @Select+ ' AND (Division=@Division) '

IF @SalesDivision<>0 AND @SalesDivision IS NOT NULL

SET @Select = @Select+ ' AND (SalesDivision=@SalesDivision) '

IF @Costcenter<>0 AND @Costcenter IS NOT NULL

SET @Select = @Select+ ' AND (Costcenter=@Costcenter) '

IF @SubAccount<>0 AND @SubAccount IS NOT NULL

SET @Select = @Select+ ' AND (SubAccount=@SubAccount) '

IF @AsOnDate<>0 AND @AsOnDate IS NOT NULL

SET @Select = @Select+ ' AND (RQDate<=@AsOnDate) '

--print @Select

Execute sp_Executesql @Select, @Param , @BudgetYear, @BudgetVersion, @Company, @Division, @CostCenter, @SubAccount, @SalesDivision, @AsOnDate

END

SET NOCOUNT OFF

RETURN