quote:

---

exec 'Yourproc'
into #tmp

---

quote:

---

Originally posted by madhivanan

quote:

---

exec 'Yourproc'
into #tmp

---

Did you mean this?

Insert into #tmp(columns) exec Yourproc


Madhivanan

Failing to plan is Planning to fail

---

CREATE PROCEDURE dbo.pr_incChemUsages
AS
SET NOCOUNT ON
/*
	++++++++++++++++++++++++++++++++++++++++
	Chemical Usages
	++++++++++++++++++++++++++++++++++++++++ 
*/
Declare @XFromDate datetime
Declare @XToDate datetime

SELECT @XFromDate = DateAdd(m,-1,ChemicalPeriod),
	@XToDate = ChemicalPeriod - 1
FROM tblCtrl
WHERE CtrlID = 'CTRL'

/*
	++++++++++++++++++++++++++++++++++++++++
	Pickup Monthend records
	++++++++++++++++++++++++++++++++++++++++ 
*/
SELECT 
	QChemical = Left(ProductID, 7),
	QSource = 'tblMonthend',
	QUsageDols = UsageDols,
	QUsageQty = UsageQty
INTO #Temp
FROM tblMonthend
WHERE MEPeriod = @XFromDate And (UsageDols <> 0 Or UsageQty <> 0)

UNION ALL
SELECT 
	'1215490B',
	'tblUsages',
	0,
	ChemUsage / 1000
FROM tblUsages
WHERE ChemPeriod = @XFromDate And ChemicalID = 'TURBOTAK'

UNION ALL
SELECT 
	'TOILSALE',
	'CALC',
	0,
	0
/*
	++++++++++++++++++++++++++++++++++++++++
	Calculate Lime to Effluent	
	++++++++++++++++++++++++++++++++++++++++ 
*/
UNION ALL
SELECT
	'LIMEEFFL',
	'CALC',
	QLimeEff = CASE 
		WHEN TEMP.UsageQty = 0 THEN 0 
		ELSE Round(ChemUsage * Round(TEMP.UsageDols / TEMP.UsageQty, 2), 0) END,
	-1 * ChemUsage
FROM tblUsages USAG
LEFT OUTER JOIN 
(
	SELECT
		MEPeriod,
		UsageDols,
		UsageQty		
	FROM tblMonthend
	WHERE MEPeriod = @XFromDate And ProductID = '1218260'
) TEMP ON USAG.ChemPeriod = TEMP.MEPeriod
WHERE ChemicalID = 'LIMETFR' AND ChemPeriod = @XFromDate
/*
	++++++++++++++++++++++++++++++++++++++++
	Calculate 'Acid to Ponds' and 'Tall Oil'	
	++++++++++++++++++++++++++++++++++++++++ 
*/
UNION ALL
SELECT
	QChemical = CASE WHEN AdjComments like '%PONDS%' THEN 'ACIDPOND' ELSE 'ACIDTOIL' END,
	'CALC',
	QUsageDols = Sum(-1 * AdjAmount),
	QUsageQty = Sum(-1 * AdjQty)
FROM tblAdjustments
WHERE ProductID = '1218910' And AdjDate Between @XFromDate And @XToDate 
	And (AdjComments like '%PONDS%' Or AdjComments like '%TALL OIL%')
GROUP BY CASE WHEN AdjComments like '%PONDS%' THEN 'ACIDPOND' ELSE 'ACIDTOIL' END 
/*
	++++++++++++++++++++++++++++++++++++++++
	Select Oxygen And Acid Records from Usage file
	Use Rates from Monthend file 
	++++++++++++++++++++++++++++++++++++++++ 
*/
UNION ALL
SELECT
	USAG.ChemicalID,
	'CALC',
	QDols = CASE
		WHEN USAG.ChemicalID IN ('1218050K','1218050D','1218050E') THEN Round(ChemUsage / 1000 * QO2Rate, 2)
		WHEN USAG.ChemicalID IN ('BWACID','PHACID') THEN Round(ChemUsage / 1000 * QAcidRate, 2)
		ELSE 0 END,
	ChemUsage / 1000
FROM tblUsages USAG
JOIN (
	SELECT MEPeriod,
		QO2Rate = Sum(CASE WHEN ProductID = '1218050' THEN UsageDols / UsageQty ELSE 0 END),
		QAcidRate = Sum(CASE WHEN ProductID = '1218910' THEN UsageDols / UsageQty ELSE 0 END)
	FROM tblMonthend
	WHERE MEPeriod = @XFromDate And ProductID IN ('1218050','1218910')
	GROUP BY MEPeriod
) J1 ON USAG.ChemPeriod = J1.MEPeriod
WHERE USAG.ChemicalID IN ('1218050K','1218050D','1218050E','BWACID','PHACID') And USAG.ChemPeriod = @XFromDate 
	And USAG.ChemUsage is not Null And USAG.ChemUsage <> 0 
/*
	++++++++++++++++++++++++++++++++++++++++
	Deduct BWACID from 1218910 Product
	++++++++++++++++++++++++++++++++++++++++ 
*/
INSERT INTO #Temp (QChemical, QSource, QUsageDols, QUsageQty)
SELECT
	'1218910',
	'CALC',
	-1 * QUsageDols,
	-1 * QUsageQty
FROM #Temp
WHERE QChemical = 'BWACID'
/*
	++++++++++++++++++++++++++++++++++++++++
	Rounding - 1218050 + 1215490
	++++++++++++++++++++++++++++++++++++++++ 
incomplete
*/ 
SELECT * 
FROM #Temp