在SQL中将数据类型varchar转换为numeric时出错

时间:2021-08-20 19:20:41

I have spent the last 3 hours trying to figure this out but I have had not luck. When executing this SP I get the below error:

我花了最后3个小时试图解决这个问题,但我没有运气。执行此SP时,我收到以下错误:

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

Here is the SP and help is really appreciated

这是SP和帮助非常感谢

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  

1 个解决方案

#1


3  

--Find the row that is not numeric and fix data

- 找到非数字的行并修复数据

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

--If you can't fix the data you will need to cast as varchar and compare

- 如果无法修复数据,则需要将其转换为varchar并进行比较

#1


3  

--Find the row that is not numeric and fix data

- 找到非数字的行并修复数据

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

--If you can't fix the data you will need to cast as varchar and compare

- 如果无法修复数据,则需要将其转换为varchar并进行比较