关于带分割符号的字符串查询问题
CREATE PROCEDURE TRD_ORDER_MAIN_DelBatchSkuFromCart
@SkuProductSupplierPKId varchar(8000), --产品ID
@MemberPKId int --会员ID
as
DECLARE @CartPKId int
select @CartPKId=a.CartPKId from TRD_CART_MAIN a with(nolock) inner join
TRD_CART_DETAIL b with(nolock) on a.CartPKId=b.CartPKId
where a.MemberPKId=CONVERT(VARCHAR,@MemberPKId)
DELETE FROM dbo.TRD_CART_DETAIL
WHERE SkuProductSupplierPKId in (@SkuProductSupplierPKId)
and @CartPKId=CartPKId
这句SQL 看似没有问题,但是如果执行,delete 会报
在将 varchar 值 ‘1,2,3,4,5’ 转换成数据类型 int 时失败。
其实此条语句在执行时,执行的是
DELETE FROM dbo.TRD_CART_DETAIL
WHERE SkuProductSupplierPKId in ('1,2,3,4,5')
and @CartPKId=CartPKId
这样执行当然出错,因为@SkuProductSupplierPKId是一个字符串,现在是以参数的形式传递。
应将这句加exec执行函数(一定要加上exec不然还会报错),改为:
exec('DELETE FROM dbo.TRD_CART_DETAIL
WHERE SkuProductSupplierPKId in ('+@SkuProductSupplierPKId+')
and '+@CartPKId+'=CartPKId')