I can't seem to get this stored proc to run properly. It won't let me insert records into it. Any suggestions?
CREATE PROCEDURE TEST_PROC
/
My Name
/
AS
BEGIN
SET NOCOUNT ON
DECLARE @MAVG2003 AS MONEY
SET @MAVG2003 = (SELECT AVG(PRICE) AS AVERAGE
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003')
CREATE TABLE #FORECAST(
CUSTOMER_ID INT,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
TOTAL_2003 MONEY,
FORECAST_2004 MONEY,
FORECAST_2005 MONEY)
INSERT INTO #FORECAST
SELECT C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
TOTAL_2003 SUM(P.PRICE) AS TOTALYEAR,
0,
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003'
GROUP BY C.CUSTOMER_ID
UPDATE #FORECAST
SET FORECAST_2004 = TOTAL_2003 * (TOTAL_2003 / @MAVG2003)
UPDATE #FORECAST
SET FORECAST_2005 = FORECAST_2004 + (FORECAST_2004 * .0105)
SELECT CUSTOMER_ID, TOTAL_2003, FORECAST_2004, FORECAST_2005
FROM #FORECAST
ORDER BY TOTAL_2003 DESC
DROP TABLE #FORECAST
RETURN 0
END
GO
