为什么用参数查询不到数据@Members,该怎么解决

为什么用参数查询不到数据@Members

SQL code

--create table Tid--(--task_id  varchar(20),--members  varchar(30)--)----create table Tname --(--[user_id] varchar(20),--username varchar(50)--)----insert  Tid--select 1,'34,35,36'--union select 2,'33,36'--insert Tname --select 33,N'劉二'--union select 34,N'張三'--union select 35,N'李四'--union select 36,N'王五'

declare @Id int, @Members nvarchar(20)
select @Members ='34,35,36'
 select username from Tname where user_id in (@Members)
 select username from Tname where user_id in (34,35,36)


[解决办法]

SQL code

declare @Id int, @Members nvarchar(20)select @Members ='34,35,36'--select username from Tname where user_id in (@Members)EXEC ('select username from Tname where user_id in ('+@Members+')')/*張三李四王五*/
[解决办法]
你这样select username from Tname where user_id in (@Members)传值进去相当于
select username from Tname where user_id in ('34,35,36')当成一个字符串了，当然没有结果了
可以
select username from Tname where CHARINDEX(',' + CAST(user_id AS VARCHAR(10)) + ',',',' + @Members + ',') > 0
[解决办法]
SQL code
declare @Id int, @Members nvarchar(20)select @Members ='34,35,36' exec('select username from Tname where user_id in ('+''''+@Members+''''+')') select username from Tname where user_id in (34,35,36)
[解决办法]
要么动态拼接，要么用CHARINDEX。 

 

查看更多 下一篇