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并进行比较