Temp Table Column Type?

Giganews Newsgroups
Subject: Temp Table Column Type?
Posted by:  jos…@norrisinc.com
Date: 23 Aug 2006

can anyone help me figure out why when i run the following stored
procedure i get the error:

(1460 row(s) affected)
Msg 245, Level 16, State 1, Procedure SP_SALESTRENDS, Line 40
Conversion failed when converting the varchar value 'X' to data type
int.

SP:

--STORED PROCEDURE FOR INVOICE TRENDS:
--To use Stored Procedure use the following code:
--EXEC SP_INSPECTIONSUMRY (MONTH), (OFFICE)
--    (OFFICE) CAN BE:  BGR FOR BANGOR, SP FOR SOUTH PORTLAND, NH FOR NEW
HAMPSHIRE, UNH FOR UNH
--    (REPORT) CAN BE:  PRODUCT CODE FOR REPORT BROKEN OUT BY PRODUCT CODE

-- EXEC SP_SALESTRENDS BGR, INVOICED, 2006, X

ALTER PROCEDURE SP_SALESTRENDS
@OFFICE VARCHAR(30),
@REPORT VARCHAR(30),
@VARYEAR INT,
@CODE VARCHAR(30)
AS

IF @REPORT='INVOICED'
SELECT YEAR(I.INVOICEDAT) AS VARYEAR, MONTH(I.INVOICEDAT) AS VARMONTH,
SUM(I.STOTAL) AMOUNT, P.PERSON, P.PRODUCT, C.DESCRIPTN
INTO #TEMP_SALESTRENDS
FROM OPENQUERY(PROJECTS, '
    SELECT PROJECT, INVOICEDAT,  STOTAL
    FROM INVSUMYR') I
LEFT JOIN
    (SELECT *
    FROM OPENQUERY(PROJECTS, '
        SELECT NUMBER, PRODUCT, PERSON
        FROM PROJMAST
        ')) P
ON (LTRIM(I.PROJECT)=LTRIM(P.NUMBER))
LEFT JOIN
    (SELECT PC, DESCRIPTN
    FROM OPENQUERY(PROJECTS, '
        SELECT PC, DESCRIPTN
        FROM PRODCODE')) C
ON (C.PC=P.PRODUCT)
GROUP BY YEAR(I.INVOICEDAT), MONTH(I.INVOICEDAT), P.PERSON, P.PRODUCT,
C.DESCRIPTN
ORDER BY VARYEAR, VARMONTH

-- INVOICED REPORT BROKEN OUT BY OFFICE

IF @REPORT='INVOICED' AND @CODE=1 AND @VARYEAR=1234 AND
@OFFICE='NORRIS'
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTRENDS
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

IF @REPORT='INVOICED' AND @CODE!=1 AND @VARYEAR=1234
    SELECT VARYEAR, VARMONTH, SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTRENDS
    WHERE PRODUCT=@CODE
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

IF @REPORT='INVOICED'AND @CODE!=1 AND @VARYEAR!=1234
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTRENDS
    WHERE PRODUCT=@CODE AND VARYEAR=@VARYEAR
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

IF @REPORT='INVOICED' AND @OFFICE='NORRIS' AND @CODE=1 AND
@VARYEAR!=1234
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTRENDS
    WHERE VARYEAR=@VARYEAR
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

IF @REPORT='INVOICED' AND @OFFICE='BGR' AND @CODE=1 AND @VARYEAR=1234
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTRENDS
    WHERE PRODUCT IN ('G', 'H', 'I', 'J', 'K', 'L')
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

IF @REPORT='INVOICED' AND @OFFICE='BGR' AND @CODE=1 AND @VARYEAR!=1234
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTRENDS
    WHERE PRODUCT IN ('G', 'H', 'I', 'J', 'K', 'L') AND VARYEAR=@VARYEAR
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

IF @REPORT='INVOICED' AND @OFFICE='SP' AND @CODE=1 AND @VARYEAR=1234
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTRENDS
    WHERE PRODUCT IN ('A', 'B', 'C', 'D', 'E', 'C', 'S', '3', '4')
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

IF @REPORT='INVOICED' AND @OFFICE='SP' AND @CODE=1 AND @VARYEAR!=1234
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTRENDS
    WHERE PRODUCT IN ('A', 'B', 'C', 'D', 'E', 'C', 'S', '3', '4') AND
VARYEAR=@VARYEAR
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

IF @REPORT='INVOICED' AND @OFFICE='NH' AND @CODE=1 AND @VARYEAR=1234
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTRENDS
    WHERE PRODUCT IN ('W', 'X', 'Y', 'N', 'O', 'P')
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

IF @REPORT='INVOICED' AND @OFFICE='NH' AND @CODE=1 AND @VARYEAR!=1234
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTRENDS
    WHERE PRODUCT IN ('W', 'X', 'Y', 'N', 'O', 'P') AND VARYEAR=@VARYEAR
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

IF @REPORT='INVOICED' AND @OFFICE='UNH' AND @CODE=1 AND @VARYEAR=1234
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTRENDS
    WHERE PRODUCT IN ('U', 'Z', 'R', 'V')
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

IF @REPORT='INVOICED' AND @OFFICE='UNH' AND @CODE=1 AND @VARYEAR!=1234
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTRENDS
    WHERE PRODUCT IN ('U', 'Z', 'R', 'V') AND VARYEAR=@VARYEAR
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

--END OF SALES TRENDS STORED PROCEDURE

thanks.

Replies