|
|
Posted by joshd on 10/02/99 11:56
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.
Navigation:
[Reply to this message]
|