exec('select ts_slaveselections into ##tmp_subhuiftype from ts_msselection where ts_masterselectionid='+@subtype)exec('select ts_name,ts_id into ##tmp_subtypelist from ts_selections a ,##tmp_subhuiftype b where a.ts_id in (b.ts_slaveselections)')ts_slaveselections的类型是:text
a.ts_id 的类型是:int服务器: 消息 306，级别 16，状态 1，行 1
不能比较或排序 text、ntext 和 image 数据类型，除非使用 IS NULL 或 LIKE 运算符。
',334,5345,234,234,2,4,234213,32322,'
否则会导致首尾的数字查询不到.
另外,因为@subtype这个变量不是表名称或列名称,所以不用动态查询.
请楼主试试以下是否符合要求:
if object_id('ts_msselection_test') is not null
drop table ts_msselection_test
if object_id('ts_selections_test') is not null
drop table ts_selections_test
----创建含有text类型列的表(注意:插入时text类型列值的首尾必须分别加额外的逗号)
create table ts_msselection_test(ts_masterselectionid int,ts_slaveselections text)
insert ts_msselection_test
select 1,',334,5345,234,234,2,4,234213,32322,' union all
select 1,',1,2,3,4,5,6,234213,32322,' union all
select 2,',9,31,32,56,57,128,' union all
select 2,',334,5345,234,234,2,4,234213,32322,'
----创建要查询的表
create table ts_selections_test(ts_id int,ts_name varchar(10))
insert ts_selections_test
select 334,'A' union all
select 5345,'B' union all
select 32322,'C' union all
select 9,'D' union all
select 128,'E' union all
select 4,'F'
----查询
declare @subtype int
set @subtype = 1
select ts_name,ts_id from ts_selections_test where ts_id in
(
select distinct ts_id
from ts_selections_test a inner join ts_msselection_test b
on patindex('%,' + cast(a.ts_id as varchar(10)) + ',%',b.ts_slaveselections) > 0
where b.ts_masterselectionid = @subtype
)
----清除测试环境
drop table ts_selections_test,ts_msselection_test