exec sp_prepexec 问题

[guo83551218]

declare @p1 int
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000)',N'delete from [login] where [empid] in (@P0)',
N'20102820'
成功

declare @p1 int
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000)',N'delete from [login] where [empid] in (@P0)',
N'20102832,20102820,20102821'
失败

为什么@P0 参数变成'20102832,20102820,20102821' 就执行不成功啊？？

[Shawn]

CREATE TABLE [login](empid INT)

--成功
declare @p1 int
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000)',N'delete from [login] where [empid] in (@P0)',
N'20102820'

--报错
declare @p1 int
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000)',N'delete from [login] where [empid] in (@P0)',
N'20102832,20102820,20102821'

--sp_prepexec 是参数化查询,它会把 "20102832,20102820,20102821" 整个字符串当成一个值,而不会像我们平时写SQL IN语法那样,把它当成3个值.
--字符串与整数型对比时,默认把字符串转换成整型再作比较,所以报错了
/*
消息 245，级别 16，状态 1，第 1 行
在将 nvarchar 值 '20102832,20102820,20102821' 转换成数据类型 int 时失败。
*/

[Andy__Huang]

多个id组合,应该这样写:

declare @p1 int
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000)',N'delete from [login] where charindex('',''+cast(empid as varchar)+'','','',''+@P0+'','')>0',
N'20102832,20102820,20102821'