Error de conversión de tipo de datos varchar numéricos en SQL

He pasado los últimos 3 horas tratando de resolver esto, pero he tenido suerte no. Al ejecutar este SP me sale el siguiente error:

Msg 8114, Level 16, State 5, Procedure sp_SPLIT_CARTON, Line 28
Error converting data type varchar to numeric.

Aquí es la SP y la ayuda es muy apreciada

USE [1_WMS]

GO 

ALTER PROCEDURE [dbo].sp_SPLIT_CARTON
    @FROM_CARTON VARCHAR(20)
    , @TO_CARTON VARCHAR(20)
    , @SKU VARCHAR(20)
    , @QTY DECIMAL
    , @USER VARCHAR(20)

AS
DECLARE 
    @DATE VARCHAR(10)
    , @TIME VARCHAR(8)
    , @SYS_CONFIG_CODE VARCHAR(5)
    , @SYS_CONFIG_VALUE INT
    , @CN_STATUS INT
    , @CN_STATUS_1 INT  --USE FOR BETWEEN STATEMENT
    , @CN_STATUS_2 INT  --USE FOR BETWEEN STATEMENT
    , @CN_STORE VARCHAR(10)

    SET @DATE = CONVERT(VARCHAR(10), GETDATE(),101);
    SET @TIME = CONVERT(VARCHAR(8), GETDATE(),114);
    SET @SYS_CONFIG_CODE = 'SPLCN';
    SET @CN_STATUS_1 = '10'; 
    --SET @CN_STATUS_2 = 20;

    --THIS IS LINE 28
/*CHECK FOR VALID CARTON STATUS BEFORE SPLITTING*/  
SELECT  @CN_STATUS = cn_status 
        , @CN_STORE = cn_store
FROM CARTON
    WHERE cn_number = @FROM_CARTON    --I BELIEVE THIS IS THE PIECE OF CODE CAUSING THE ISSUE


IF @CN_STATUS = @CN_STATUS_1
    BEGIN

        /*CHECK FOR SYSTEM CONFIGURATION*/  
        SELECT @SYS_CONFIG_VALUE = sys_value FROM SYS_CONFIG
            WHERE sys_code = @SYS_CONFIG_CODE

        IF @SYS_CONFIG_VALUE = 1
            BEGIN
                /*REMOVE SKU FROM CURRENT CARTON*/
                DELETE FROM CARTON_DETAIL 
                    WHERE cd_carton_number = @FROM_CARTON
                        AND cd_barcode = @SKU

                /*UPDATE THE CARTON HEADER*/
                UPDATE CARTON
                    SET cn_packed_qty = cn_packed_qty - @QTY
                        , cn_modify_date = @DATE
                        , cn_modify_time = @TIME
                        , cn_modify_by = @USER
                    WHERE cn_number = @FROM_CARTON
                        AND cn_status BETWEEN @CN_STATUS_1 AND @CN_STATUS_2 

                /*CREATE NEW CARTON HEADER*/                    
                INSERT INTO CARTON
                    (
                        cn_number
                    )
                    VALUES
                    (
                        @FROM_CARTON
                    )

                /*CREATE CARTON DETAIL*/
                INSERT INTO CARTON_DETAIL
                    (
                        cd_carton_number
                    )
                    VALUES
                    (
                        @TO_CARTON
                    )                   

            END

        ELSE IF @SYS_CONFIG_VALUE = 0
            BEGIN
                /*REMOVE SKU FROM CURRENT CARTON*/
                DELETE FROM CARTON_DETAIL 
                    WHERE cd_carton_number = @FROM_CARTON
                        AND cd_barcode = @SKU

                /*UPDATE THE CARTON HEADER*/
                UPDATE CARTON
                    SET cn_packed_qty = cn_packed_qty - @QTY
                        , cn_modify_date = @DATE
                        , cn_modify_time = @TIME
                        , cn_modify_by = @USER
                    WHERE cn_number = @FROM_CARTON
                        AND cn_status BETWEEN @CN_STATUS_1 AND @CN_STATUS_2 

                /*GET THE NEXT CARTON FROM COUNTERS*/
                SELECT @TO_CARTON = counter_current FROM COUNTERS WHERE counter_name = (
                SELECT DISTINCT so_counter_name FROM STORES WHERE SO_NUMBER = (
                SELECT DISTINCT cn_store FROM CARTON WHERE cn_number = @FROM_CARTON))

                /*UPDATE THE COUNTER AFTER GETTING THE NEXT CARTON NUMBER*/
                UPDATE COUNTERS SET counter_current = counter_current + 1
                                    , counter_next = counter_next + 1
                    WHERE counter_name = (SELECT DISTINCT so_counter_name FROM STORES WHERE SO_NUMBER = (
                                            SELECT DISTINCT cn_store FROM CARTON WHERE cn_number = @FROM_CARTON))

                /*CREATE NEW CARTON HEADER*/
                DECLARE 
                    @CN_NUMBER VARCHAR(20)              , @CN_PICKTICKET VARCHAR(20)        , @2ndCN_STORE VARCHAR(10)      , @CN_LOAD_NUMBER VARCHAR(20)
                    , @CN_SHIPMENT_NUMBER VARCHAR(20)   , @CN_MANIFEST_NUMBER VARCHAR(20)   , @CN_PACKED_QTY DECIMAL        , @CN_TRACKING_NUMBER VARCHAR(20)
                    , @CN_TYPE VARCHAR(5)               , @CN_PACK_TYPE VARCHAR(5)          , @CN_ROUTE VARCHAR(5)          , @CN_SHIP_VIA VARCHAR(5)
                    , @CN_BOL VARCHAR(20)               , @CN_MBOL VARCHAR(20)              , @CN_PARCEL_NUMBER VARCHAR(10) , @CN_TRAILER_NUMBER VARCHAR(10)
                    , @CN_AREA VARCHAR(10)              , @CN_ZONE VARCHAR(10)              , @CN_AISLE VARCHAR(10)         , @CN_LEVEL VARCHAR(10)
                    , @CN_POSITION VARCHAR(10)          , @CN_HEIGHT DECIMAL                , @CN_WIDTH DECIMAL             , @CN_DIMENSION DECIMAL
                    , @CN_WEIGHT DECIMAL                , @CN_VOLUME DECIMAL                , @2ndCN_STATUS INT             , @CN_ADDRESS VARCHAR(150)
                    , @CN_ADDRESS_1 VARCHAR(150)        , @CN_CITY VARCHAR(50)              , @CN_STATE VARCHAR(50)         , @CN_ZIP_CODE VARCHAR(20)
                    , @CN_COUNTRY VARCHAR(50)           , @CN_MISC7 VARCHAR(50)             , @CN_MISC8 VARCHAR(50)         , @CN_MISC9 VARCHAR(50)
                    , @CN_MISC10 VARCHAR(50)                                    

                SET @CN_NUMBER = @TO_CARTON         SET @2ndCN_STORE = @CN_STORE            SET @CN_LOAD_NUMBER = ''
                SET @CN_SHIPMENT_NUMBER = ''        SET @CN_MANIFEST_NUMBER = ''            SET @CN_PACKED_QTY = ''
                SET @CN_TRACKING_NUMBER = ''        SET @CN_TYPE = 'SPLIT'                  SET @CN_PACK_TYPE = 'SPLITTED'
                SET @CN_ROUTE = ''                  SET @CN_SHIP_VIA = ''                   SET @CN_BOL = ''
                SET @CN_MBOL = ''                   SET @CN_PARCEL_NUMBER = ''              SET @CN_TRAILER_NUMBER = ''
                SET @CN_AREA = ''                   SET @CN_ZONE = ''                       SET @CN_AISLE = ''
                SET @CN_LEVEL = ''                  SET @CN_POSITION = ''                   SET @CN_HEIGHT = ''
                SET @CN_WIDTH = ''                  SET @CN_DIMENSION = ''                  SET @CN_WEIGHT = ''
                SET @CN_VOLUME = ''                 SET @2ndCN_STATUS = '10'                SET @CN_MISC7 = ''
                SET @CN_MISC8 = ''                  SET @CN_MISC9 = ''                      SET @CN_MISC10 = '' 

                /*GET STORE INFORMATION*/
                SELECT @CN_ADDRESS = so_address
                        , @CN_ADDRESS_1 = so_address_1
                        , @CN_CITY = so_city
                        , @CN_STATE = so_state
                        , @CN_ZIP_CODE = so_zip_code
                        , @CN_COUNTRY = so_country
                    FROM STORES
                    WHERE so_number = @CN_STORE

                EXECUTE sp_CREATE_CARTON
                    @CN_NUMBER              , @CN_PICKTICKET        , @CN_STORE             , @CN_LOAD_NUMBER       , @CN_SHIPMENT_NUMBER
                    , @CN_MANIFEST_NUMBER   , @CN_PACKED_QTY        , @CN_TRACKING_NUMBER   , @CN_TYPE              , @CN_PACK_TYPE
                    , @CN_ROUTE             , @CN_SHIP_VIA          , @CN_BOL               , @CN_MBOL              , @CN_PARCEL_NUMBER
                    , @CN_TRAILER_NUMBER    , @CN_AREA              , @CN_ZONE              , @CN_AISLE             , @CN_LEVEL
                    , @CN_POSITION          , @CN_HEIGHT            , @CN_WIDTH             , @CN_DIMENSION         , @CN_WEIGHT
                    , @CN_VOLUME            , @CN_STATUS            , @CN_ADDRESS           , @CN_ADDRESS_1         , @CN_CITY
                    , @CN_STATE             , @CN_ZIP_CODE          , @CN_COUNTRY           , @CN_MISC7         , @CN_MISC8
                    , @CN_MISC9             , @CN_MISC10            , @USER



                INSERT INTO CARTON
                    (
                        cn_number
                    )
                    VALUES
                    (
                        @TO_CARTON
                    )

                /*CREATE CARTON DETAIL*/
                INSERT INTO CARTON_DETAIL
                    (
                        cd_carton_number
                    )
                    VALUES
                    (
                        @TO_CARTON
                    )                   
            END 
    END
--ELSE
--  BEGIN
--      EXECUTE sp_CREATE_ERROR_MESSAGE
--          @ER_TYPE
--  END 

GO  
  • Que es la línea 28? resalte el código que genera el error.
  • Puedo comentar donde la línea 28 se inicia y el trozo de código que está causando el problema
  • ¿Por qué tienes el parámetro @FROM_CARTON como un varchar? Parece que debe ser un entero.
  • Por qué crees que @FROM_CARTON debe ser de tipo INT?
  • Algunos filas tienen una cn_status que no numérico, o cn_number es un número y @from_carton no lo es. Simplemente ejecutar la consulta con la mano y debería estar claro.
  • Porque la comparamos con una columna cn_number. El número de parte de el nombre de la columna y el hecho de que usted consigue Error converting data type varchar to numeric. en esa línea.
  • En ese caso, sólo ejecutar seleccione cn_status,cn_store de la caja de cartón donde cn_number = @from_carton. Al menos se llega a saber si usted es bienvenido con el mismo error o consiguió pasar.

InformationsquelleAutor jorame | 2012-09-17

1 Kommentar

  1. 4

    –Encontrar la fila que no es numérico y corregir los datos

    SELECT *
    FROM CARTON
        WHERE ISNUMERIC(cn_number) != 1 OR 
              ISNUMERIC(cn_status) != 1

    –Si usted no puede corregir la información que usted necesita para lanzar como varchar y comparar

    • Muchas gracias por tu ayuda. He encontrado 3 filas donde la cn_status es NULL

Kommentieren Sie den Artikel

Bitte geben Sie Ihren Kommentar ein!
Bitte geben Sie hier Ihren Namen ein

Pruebas en línea