if @KMan Is Not Null Begin set @CondiTion=isnull(@CondiTion+' and','') + ' KDMan='+@KMan Endif @CustID Is Not Null Begin set @CondiTion=isnull(@CondiTion+' and','') + ' CustID='+ltrim(@CustID) Endif @CustMaterialID Is Not Null Begin set @CondiTion=isnull(@CondiTion+' and','') + ' CustMaterialID='+ltrim(@CustMaterialID) Endif @Type Is Not Null Begin set @CondiTion=isnull(@CondiTion+' and','') + ' Type=' + @Type End
可以的,另外上面你有一个地方写错了,改了一下 if @VipOrNot Is Not Null Begin @CondiTion='VIPorNot='+@VipOrNot end
where id=isnull(@id,id) and col1=isnull(@col,col)--类似,如果@变量为空,则 id=id ,如果没有null数据,就可以直接使用了--如果有的话 where isnull(id,'')=isnull(@id,isnull(id,'')) and isnull(col,'')=isnull(@col,isnull(col,''))
可以用isnull来代替 case when ... is null then ...
set @CondiTion='KDMan='+isnull(@KMan,'KDMan') set @CondiTion=@CondiTion+' and CustID='+isnull(CustID,'CustID') set @CondiTion=@CondiTion+' and CustMaterialID='+isnull(CustMaterialID,'CustMaterialID') set @CondiTion=@CondiTion+' and Type='+isnull(Type,'Type')
现在遇到一个问题就是查询时 Select 1,2,3 from #tmpSrc Where @CondiTion Order bay a,b,c 的时候提示 Order 附近有语法错误
Select 1,2,3 from #tmpSrc Where @CondiTion Order bay a,b,c exec('Select 1,2,3 from #tmpSrc Where '+@CondiTion+' Order by a,b,c')
exec('Select 1,2,3 from #tmpSrc Where '+ @CondiTion+' Order bay a,b,c')
exec ('Select 1,2,3 from #tmpSrc Where '+@CondiTion+' Order by a,b,c')
if @KMan Is Not Null
Begin
set @CondiTion=isnull(@CondiTion+' and','') + ' KDMan='+@KMan
Endif @CustID Is Not Null
Begin
set @CondiTion=isnull(@CondiTion+' and','') + ' CustID='+ltrim(@CustID)
Endif @CustMaterialID Is Not Null
Begin
set @CondiTion=isnull(@CondiTion+' and','') + ' CustMaterialID='+ltrim(@CustMaterialID)
Endif @Type Is Not Null
Begin
set @CondiTion=isnull(@CondiTion+' and','') + ' Type=' + @Type
End
if @VipOrNot Is Not Null
Begin
@CondiTion='VIPorNot='+@VipOrNot
end
id=isnull(@id,id)
and
col1=isnull(@col,col)--类似,如果@变量为空,则 id=id ,如果没有null数据,就可以直接使用了--如果有的话
where
isnull(id,'')=isnull(@id,isnull(id,''))
and
isnull(col,'')=isnull(@col,isnull(col,''))
case when ... is null then ...
set @CondiTion=@CondiTion+' and CustID='+isnull(CustID,'CustID')
set @CondiTion=@CondiTion+' and CustMaterialID='+isnull(CustMaterialID,'CustMaterialID')
set @CondiTion=@CondiTion+' and Type='+isnull(Type,'Type')
Select 1,2,3 from #tmpSrc Where @CondiTion Order bay a,b,c
的时候提示 Order 附近有语法错误
exec('Select 1,2,3 from #tmpSrc Where '+@CondiTion+' Order by a,b,c')