Temp Table Column Type?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • joshd@norrisinc.com
    #1

    Temp Table Column Type?

    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_INSPECTIONSU MRY (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='INVOIC ED'
    SELECT YEAR(I.INVOICED AT) AS VARYEAR, MONTH(I.INVOICE DAT) AS VARMONTH,
    SUM(I.STOTAL) AMOUNT, P.PERSON, P.PRODUCT, C.DESCRIPTN
    INTO #TEMP_SALESTREN DS
    FROM OPENQUERY(PROJE CTS, '
    SELECT PROJECT, INVOICEDAT, STOTAL
    FROM INVSUMYR') I
    LEFT JOIN
    (SELECT *
    FROM OPENQUERY(PROJE CTS, '
    SELECT NUMBER, PRODUCT, PERSON
    FROM PROJMAST
    ')) P
    ON (LTRIM(I.PROJEC T)=LTRIM(P.NUMB ER))
    LEFT JOIN
    (SELECT PC, DESCRIPTN
    FROM OPENQUERY(PROJE CTS, '
    SELECT PC, DESCRIPTN
    FROM PRODCODE')) C
    ON (C.PC=P.PRODUCT )
    GROUP BY YEAR(I.INVOICED AT), MONTH(I.INVOICE DAT), P.PERSON, P.PRODUCT,
    C.DESCRIPTN
    ORDER BY VARYEAR, VARMONTH


    -- INVOICED REPORT BROKEN OUT BY OFFICE

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

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

    IF @REPORT='INVOIC ED'AND @CODE!=1 AND @VARYEAR!=1234
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTREN DS
    WHERE PRODUCT=@CODE AND VARYEAR=@VARYEA R
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

    IF @REPORT='INVOIC ED' AND @OFFICE='NORRIS ' AND @CODE=1 AND
    @VARYEAR!=1234
    SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
    FROM #TEMP_SALESTREN DS
    WHERE VARYEAR=@VARYEA R
    GROUP BY VARYEAR, VARMONTH
    ORDER BY VARYEAR, VARMONTH

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

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

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

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

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

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

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

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



    --END OF SALES TRENDS STORED PROCEDURE



    thanks.

    Tags: None
    • Dan Guzman
      #2
      Re: Temp Table Column Type?

      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@norrisin c.comwrote in message
      news:1156380999 .089415.9250@m7 9g2000cwm.googl egroups.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_INSPECTIONSU MRY (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='INVOIC ED'
      SELECT YEAR(I.INVOICED AT) AS VARYEAR, MONTH(I.INVOICE DAT) AS VARMONTH,
      SUM(I.STOTAL) AMOUNT, P.PERSON, P.PRODUCT, C.DESCRIPTN
      INTO #TEMP_SALESTREN DS
      FROM OPENQUERY(PROJE CTS, '
      SELECT PROJECT, INVOICEDAT, STOTAL
      FROM INVSUMYR') I
      LEFT JOIN
      (SELECT *
      FROM OPENQUERY(PROJE CTS, '
      SELECT NUMBER, PRODUCT, PERSON
      FROM PROJMAST
      ')) P
      ON (LTRIM(I.PROJEC T)=LTRIM(P.NUMB ER))
      LEFT JOIN
      (SELECT PC, DESCRIPTN
      FROM OPENQUERY(PROJE CTS, '
      SELECT PC, DESCRIPTN
      FROM PRODCODE')) C
      ON (C.PC=P.PRODUCT )
      GROUP BY YEAR(I.INVOICED AT), MONTH(I.INVOICE DAT), P.PERSON, P.PRODUCT,
      C.DESCRIPTN
      ORDER BY VARYEAR, VARMONTH
      >
      >
      -- INVOICED REPORT BROKEN OUT BY OFFICE
      >
      IF @REPORT='INVOIC ED' AND @CODE=1 AND @VARYEAR=1234 AND
      @OFFICE='NORRIS '
      SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
      FROM #TEMP_SALESTREN DS
      GROUP BY VARYEAR, VARMONTH
      ORDER BY VARYEAR, VARMONTH
      >
      IF @REPORT='INVOIC ED' AND @CODE!=1 AND @VARYEAR=1234
      SELECT VARYEAR, VARMONTH, SUM(AMOUNT) AS AMOUNT
      FROM #TEMP_SALESTREN DS
      WHERE PRODUCT=@CODE
      GROUP BY VARYEAR, VARMONTH
      ORDER BY VARYEAR, VARMONTH
      >
      IF @REPORT='INVOIC ED'AND @CODE!=1 AND @VARYEAR!=1234
      SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
      FROM #TEMP_SALESTREN DS
      WHERE PRODUCT=@CODE AND VARYEAR=@VARYEA R
      GROUP BY VARYEAR, VARMONTH
      ORDER BY VARYEAR, VARMONTH
      >
      IF @REPORT='INVOIC ED' AND @OFFICE='NORRIS ' AND @CODE=1 AND
      @VARYEAR!=1234
      SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
      FROM #TEMP_SALESTREN DS
      WHERE VARYEAR=@VARYEA R
      GROUP BY VARYEAR, VARMONTH
      ORDER BY VARYEAR, VARMONTH
      >
      IF @REPORT='INVOIC ED' AND @OFFICE='BGR' AND @CODE=1 AND @VARYEAR=1234
      SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
      FROM #TEMP_SALESTREN DS
      WHERE PRODUCT IN ('G', 'H', 'I', 'J', 'K', 'L')
      GROUP BY VARYEAR, VARMONTH
      ORDER BY VARYEAR, VARMONTH
      >
      IF @REPORT='INVOIC ED' AND @OFFICE='BGR' AND @CODE=1 AND @VARYEAR!=1234
      SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
      FROM #TEMP_SALESTREN DS
      WHERE PRODUCT IN ('G', 'H', 'I', 'J', 'K', 'L') AND VARYEAR=@VARYEA R
      GROUP BY VARYEAR, VARMONTH
      ORDER BY VARYEAR, VARMONTH
      >
      IF @REPORT='INVOIC ED' AND @OFFICE='SP' AND @CODE=1 AND @VARYEAR=1234
      SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
      FROM #TEMP_SALESTREN DS
      WHERE PRODUCT IN ('A', 'B', 'C', 'D', 'E', 'C', 'S', '3', '4')
      GROUP BY VARYEAR, VARMONTH
      ORDER BY VARYEAR, VARMONTH
      >
      IF @REPORT='INVOIC ED' AND @OFFICE='SP' AND @CODE=1 AND @VARYEAR!=1234
      SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
      FROM #TEMP_SALESTREN DS
      WHERE PRODUCT IN ('A', 'B', 'C', 'D', 'E', 'C', 'S', '3', '4') AND
      VARYEAR=@VARYEA R
      GROUP BY VARYEAR, VARMONTH
      ORDER BY VARYEAR, VARMONTH
      >
      IF @REPORT='INVOIC ED' AND @OFFICE='NH' AND @CODE=1 AND @VARYEAR=1234
      SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
      FROM #TEMP_SALESTREN DS
      WHERE PRODUCT IN ('W', 'X', 'Y', 'N', 'O', 'P')
      GROUP BY VARYEAR, VARMONTH
      ORDER BY VARYEAR, VARMONTH
      >
      IF @REPORT='INVOIC ED' AND @OFFICE='NH' AND @CODE=1 AND @VARYEAR!=1234
      SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
      FROM #TEMP_SALESTREN DS
      WHERE PRODUCT IN ('W', 'X', 'Y', 'N', 'O', 'P') AND VARYEAR=@VARYEA R
      GROUP BY VARYEAR, VARMONTH
      ORDER BY VARYEAR, VARMONTH
      >
      IF @REPORT='INVOIC ED' AND @OFFICE='UNH' AND @CODE=1 AND @VARYEAR=1234
      SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
      FROM #TEMP_SALESTREN DS
      WHERE PRODUCT IN ('U', 'Z', 'R', 'V')
      GROUP BY VARYEAR, VARMONTH
      ORDER BY VARYEAR, VARMONTH
      >
      IF @REPORT='INVOIC ED' AND @OFFICE='UNH' AND @CODE=1 AND @VARYEAR!=1234
      SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
      FROM #TEMP_SALESTREN DS
      WHERE PRODUCT IN ('U', 'Z', 'R', 'V') AND VARYEAR=@VARYEA R
      GROUP BY VARYEAR, VARMONTH
      ORDER BY VARYEAR, VARMONTH
      >
      >
      >
      --END OF SALES TRENDS STORED PROCEDURE
      >
      >
      >
      thanks.
      >

        Comment

        • Dan Guzman
          #3
          Re: Temp Table Column Type?

          I see that you are a bit loose about this but it's a good practice to
          always enclose literals in single quotes.
          To clarify, I mean enclose *character* literals in single quotes.

          --
          Hope this helps.

          Dan Guzman
          SQL Server MVP


            Comment

            • joshd@norrisinc.com
              #4
              Re: Temp Table Column Type?

              Thanks, I corrected the syntax on the @ code, and it is working now.


              Dan Guzman wrote:
              I see that you are a bit loose about this but it's a good practice to
              always enclose literals in single quotes.
              >
              To clarify, I mean enclose *character* literals in single quotes.
              >
              --
              Hope this helps.
              >
              Dan Guzman
              SQL Server MVP

                Comment

                • joshd@norrisinc.com
                  #5
                  Re: Temp Table Column Type?

                  Thanks, I corrected the syntax on the @ code, and it is working now.


                  Dan Guzman wrote:
                  I see that you are a bit loose about this but it's a good practice to
                  always enclose literals in single quotes.
                  >
                  To clarify, I mean enclose *character* literals in single quotes.
                  >
                  --
                  Hope this helps.
                  >
                  Dan Guzman
                  SQL Server MVP

                    Comment

                    Previous template Next
                    Working...