22,209
社区成员
发帖
与我相关
我的任务
分享
select * from TableA as A inner join TableB as B
on A.Id = B.Id
where
B.Type=(case
when @A is null or @A='' then B.type else @A end)
and
B.Type=(case
when @B is null or @B='' then B.type else @B end)
AND
B.Type=(case
when @C is null or @C='' then B.type else @C end)
--错了,试下这个
declare @sql nvarchar(max)
declare @a varchar(10),@b varchar(10),@c varchar(10);
select @a='2',@b='',@c='';
select @sql='select * from TableA as A inner join TableB as B
on A.Id = B.Id AND
((('''+isnull(@a,'''')+'''<>'''' and B.Type=1) or ('''+isnull(@a,'''')+'''='''' and 1=1))
and (('''+isnull(@b,'''')+'''<>'''' and B.Type=2) or ('''+isnull(@b,'''')+'''='''' and 1=1))
and (('''+isnull(@c,'''')+'''<>'''' and B.Type=3) or ('''+isnull(@c,'''')+'''='''' and 1=1)))'
print @sql
select * from TABLEA a join TABLEB b on a.id=b.id
where ( len(a.A)=0 or a.A is null or a.A=@A )
and ( len(a.B)=0 or a.B is null or a.B=@B )
and ( len(a.C)=0 or a.C is null or a.C=@C )
declare @sql nvarchar(max)
declare @a varchar(10),@b varchar(10),@c varchar(10);
select @a='2',@b='',@c='';
select @sql='select * from TableA as A inner join TableB as B
on A.Id = B.Id AND
((('''+isnull(@a,'''')+'''<>'''' and B.Type=1) or 1=1)
and (('''+isnull(@b,'''')+'''<>'''' and B.Type=2) or 1=1)
and (('''+isnull(@c,'''')+'''<>'''' and B.Type=3) or 1=1))'
exec(@sql)