title: Error converting data type varchar to numeric in SQL
body: <p>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:</p>
  
  <pre><code>Msg 8114, Level 16, State 5, Procedure sp_SPLIT_CARTON, Line 28
  Error converting data type varchar to numeric.
  </code></pre>
  
  <p>Here is the SP and help is really appreciated</p>
  
  <pre><code>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  
  </code></pre>