|
|
Posted by Dan Guzman on 10/01/48 11:56
> Conversion failed when converting the varchar value 'X' to data type
> int.
It looks to me like the problem code is:
> @CODE=1
Here, you are comparing value 'X' (varchar(30)) to 1 (integer). Because
integer has a higher data type precedence than varchar, SQL Server attempts
to convert the 'X' to integer and you get the error.
I see that you are a bit loose about this but it's a good practice to always
enclose literals in single quotes. For example:
EXEC SP_SALESTRENDS 'BGR', 'INVOICED', 2006, 'X'
and
@CODE='1'
--
Hope this helps.
Dan Guzman
SQL Server MVP
<joshd@norrisinc.com> wrote in message
news:1156380999.089415.9250@m79g2000cwm.googlegroups.com...
> 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]
|