将varchar值“1,3”转换为SQL Server中的数据类型int时转换失败

In the below query when i try to delete comma separated values it throw a error "Conversion failed when converting the varchar value '1,3' to data type int.".Pls help me to overcome this issue.

在下面的查询中,当我尝试删除逗号分隔值时,它会抛出错误“将varchar值'1,3'转换为数据类型int时转换失败。”请帮助我克服此问题。

ALTER PROCEDURE [dbo].[spDeleteCustomerGroupLocationMap]
    -- Add the parameters for the stored procedure here
    @i_LocationID int,
    @i_CustomerGroupID varchar

    --WITH ENCRYPTION
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF EXISTS (SELECT 1 FROM CustomerGroupLocationMap WHERE LocationID = @i_LocationID AND CustomerGroupID = @i_CustomerGroupID)
    BEGIN

        DELETE CustomerGroupLocationMap WHERE  LocationID = @i_LocationID AND CustomerGroupID = (SELECT * FROM dbo.CSVToTable( @i_CustomerGroupID));

    END
END

1 个解决方案

#1

most probably the error is comming from here. Try changing it to

最有可能的错误来自这里。尝试将其更改为

DELETE CustomerGroupLocationMap 
WHERE  LocationID = @i_LocationID 
AND    CustomerGroupID IN 
       (
           SELECT [column name] 
           FROM   dbo.CSVToTable( @i_CustomerGroupID)
       );

replace "[column name]" with the actual column name from CSVToTable

将“[column name]”替换为CSVToTable中的实际列名

#1