sqlserver in子句的使用?--如何实现CorpIDSet作为ID集合使用,能在存储过程中正确执行如一般SQL效果?
CREATE TABLE [dbo].[tlCorp] (
[CorpID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,(关键字)//注意,是int
[CorpName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CorpIDSet] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL //注意,是varchar
)
CorpID CorpName CorpIDSet
1 s1 0
2 s2 1
3 s3 1
4 s4 1
5 s5 1,2
使用如下SQL,结果是2
select count(*) from tlCorp where CorpID in
(select CorpIDSet from tlCorp where CorpID=5 )
但使用如下(存储过程一部分),放入分析查询器执行,结果却为0,和SQL结果完全不同
select @CorpID=5
select @CorpIDSrc=1
select count(*) from tlCorp where CorpID in
(select CorpIDSet from tlCorp where CorpID=@CorpID ) and CorpID=@CorpIDSrc
若修改为
select @CorpID=4//修改处
select @CorpIDSrc=1
select count(*) from tlCorp where CorpID in
(select CorpIDSet from tlCorp where CorpID=@CorpID ) and CorpID=@CorpIDSrc
结果为1
问:如何实现CorpIDSet作为ID集合使用,能在存储过程中正确执行如一般SQL效果?