时间戳

首页 > 解决方案 > 将 varchar 值“01-4001”转换为数据类型 int 时转换失败

sql-server - 将 varchar 值“01-4001”转换为数据类型 int 时转换失败

问题描述

我的存储过程如下：

DECLARE @LOCATION_ID VARCHAR(MAX) = 3,  
        @INVENTORY_ITEM_ID VARCHAR(MAX) = '01-4001' ,
        @CALLFROM VARCHAR(MAX) = 'FRAMES' --Requested by VIkram Dated 29jan 2020 For MBT :- 25401,25402  

BEGIN  
    SET NOCOUNT ON;  

    BEGIN TRY  
        DECLARE @TABLE_LOCATION_ID TABLE    
                                   (  
                                       ID INT,   
                                       LOCATION_ID INT  
                                   )   

        INSERT INTO @TABLE_LOCATION_ID  
            SELECT * 
            FROM dbo.SPLIT(@LOCATION_ID, ',')  

        SELECT * FROM @TABLE_LOCATION_ID

        DECLARE @TABLE_INVENTORY_ITEM_ID TABLE    
                                         (  
                                             ID INT,   
                                             INVENTORY_ITEM_ID VARCHAR(MAX) -- 2189
                                         )   

        INSERT INTO @TABLE_INVENTORY_ITEM_ID  
            SELECT * 
            FROM dbo.SPLIT(@INVENTORY_ITEM_ID, ',')  

        SELECT * FROM @TABLE_INVENTORY_ITEM_ID  

        SELECT DISTINCT LOCATION_ID 
        FROM @TABLE_LOCATION_ID

        IF('FRAMES' = 'FRAMES')  
        BEGIN  
            --  declare @LOCATION_ID int ,
            --@INVENTORY_ITEM_ID  VARCHAR(MAX);
            SELECT DISTINCT   
                I.[UPC_ID],  
                I.INVENTORY_ITEM_ID,  
                IP.LOCATION_ID,  
                PL.LOCATION_NAME,  
                F.MANUFACTURER,   
                (CASE WHEN F.BRAND IS NULL THEN '' ELSE F.BRAND END)  BRAND,  
                F.COLOR_NAME,  
                F.COLOR_TEMPLE,  
                F.[FRAME_TEMPLE],  
                F.FRAME_EYE_SIZE,  
                F.[FRAME_DBL],  
                F.[COLLECTION] ,  
                F.MODEL_NO ,  
                [DESCRIPTION],  
                IP.RETAIL_PRICE ,  
                IP.WHOLE_SALE AS WHOLE_SALE_PRICE  
            FROM 
                PMS_INVENTORY_ITEM_DETAILS I  
            INNER JOIN 
                PMS_INVENTORY_ITEM_PRICING_DETAILS IP ON I.INVENTORY_ITEM_ID = IP.INVENTORY_ITEM_ID  
            INNER JOIN 
                PMS_FRAME_SPECIFICATIONS F ON I.INVENTORY_ITEM_ID = F.INVENTORY_ITEM_ID  
            LEFT JOIN 
                PRACTICE_LOCATIONS PL ON PL.PRACTICE_LOCATION_ID = IP.LOCATION_ID  
            LEFT JOIN 
                [PMS_PURCHASE_ORDER_ITEMS] P ON P.UPC_ID = I.UPC_ID                          -- MBT#21084 Added by Trupti  
            LEFT JOIN 
                [PMS_PURCHASE_ORDERS] PO ON PO. PURCHASE_ORDER_ID = P.PURCHASE_ORDER_ID  
            WHERE   
                (IP.LOCATION_ID IN (3) OR  @LOCATION_ID IS NULL)  
                --AND (I.UPC_ID IN (SELECT DISTINCT INVENTORY_ITEM_ID FROM @TABLE_INVENTORY_ITEM_ID ) OR  @INVENTORY_ITEM_ID IS NULL)  
                AND ((I.UPC_ID IN ('01-4001') OR  @INVENTORY_ITEM_ID IS NULL)  
                      OR (PO.PO_NO IN (SELECT CAST(DATA AS INTEGER) 
                                       FROM dbo.SPLIT(@INVENTORY_ITEM_ID, ',')) OR @INVENTORY_ITEM_ID IS NULL))  
                AND (I.INVENTORY_CATEGORY = 'FRAMES')  
                AND (IP.IS_ACTIVE = 1)   
                AND (I.IS_ACTIVE = 1)   
            --ORDER BY   IP.LAST_SOLD_DATE  
            --OFFSET (@PAGE_NO - 1) * @PAGE_SIZE ROWS FETCH NEXT @PAGE_SIZE ROWS ONLY  
        END  
    END TRY   
    BEGIN CATCH   
        SELECT   
            ERROR_NUMBER() AS ErrorNumber,  
            ERROR_MESSAGE() AS ErrorMessage;  
    END CATCH  
END

如果我执行上面的整个代码，那么下图显示的输出有错误

将 varchar 值“01-4001”转换为数据类型 int 时转换失败。

在此处输入图像描述

但如果只执行选择查询，那么它工作正常。没有错误。

我的数据类型是Varchar(max). Inventory_Item_id = '01-4001'

如果我将inventory_item_id 设置为'2189'，那么整个执行不会引发错误。

网络中的响应错误，例如：

""error":true,"message":"索引超出范围。必须为非负数且小于集合的大小。\r\n参数名称: index""

在表中的PMS_INVENTORY_ITEM_DETAILS描述INVENTORY_ITEM_ID是 int 长度 4，UPC_ID 是varchar长度 -1。

标签： sql-server

解决方案

错误发生在这里：

INSERT INTO @TABLE_INVENTORY_ITEM_ID  
SELECT * FROM  DBO.SPLIT(@INVENTORY_ITEM_ID, ',')

您为 split 函数提供的分隔符是,而不是-结果将01-4001再次出现（而不是预期的01and 4001），然后将其转换为整数会失败。

要修复，请-用作分隔符。

推荐阅读