SQL代码变量查询拼接

declare @BDate datetime,@EDate datetime,@KDGS nvarchar(50),@KDDH nvarchar(100),@JJDept nvarchar(50),@JJXM nvarchar(50),@SJXM nvarchar(50),@SJDZ nvarchar(100),@CDDept nvarchar(50),@CDXM nvarchar(50),@Incident int,@Status int,@lx int
set @BDate=?
set @EDate=?
set @KDGS=?
set @KDDH=?
set @JJDept=?
set @JJXM=?
set @SJXM=?
set @SJDZ=?
set @CDDept=?
set @CDXM=?
set @Incident=?
set @Status=?
set @lx=? 

SET IMPLICIT_TRANSACTIONS off 
--查询10天的数据
if (@BDate is null or @BDate='')
    begin 
        set @BDate=dateadd(dd,-10,getdate())
        set @EDate=getdate()
    end
if ( isnull(@BDate,'')!='' and isnull(@EDate,'')='')
        set @EDate=getdate()

Declare @StrSql nvarchar(2000) 
set @StrSql='
select a.status,c.a3 jjxm
,case when isnull(c.a5,'''')<>'''' and c.a5<>''其它'' then c.a5 else f.Name end a4
,a.a1,a.a2,d.a3 cdxm
,case when isnull(d.a5,'''')<>'''' and d.a5<>''其它'' then d.a5 else b.Name end deptName,a.a6 sjxm
,a.a7,a.a8,a.a9,a.a10,a.a11,a.a12,a.a15,a.guid,e.ProcessName,e.Incident,case when a.a14=1 then a.a12 else 0 end bxfy into #T
from t_temp1 a 
    Left Join t_t_temp2 e on a.guid=e.fk_guid
    inner join t_t_temp3 c on c.username=a.a3
    inner join t_t_temp4 d on d.username=a.a5 
    Left Join dept b on b.guid=d.deptid 
    Left Join dept f on c.deptid=f.guid
where  (a.a10 between '''+Convert(nvarchar(10),@BDate,120)+''' and '''+ Convert(nvarchar(10),@EDate,120)+''')'  

if isnull(@KDGS,'')!=''  
 set @StrSql=@StrSql+' and  a.a1='''+@KDGS+''' ' 

if isnull(@KDDH,'')!=''  
 set @StrSql=@StrSql+' and  a.a2 like ''%'+@KDDH+'%'' '

if isnull(@JJXM,'')!=''  
 set @StrSql=@StrSql+' and  c.a3='''+@JJXM+''' '  

if isnull(@SJXM,'')!=''  
 set @StrSql=@StrSql+' and  a.a6='''+@SJXM+''' '   

if isnull(@SJDZ,'')!=''  
 set @StrSql=@StrSql+' and  a.a8  like ''%'+@SJDZ+'%'' '

if isnull(@CDXM,'')!=''  
 set @StrSql=@StrSql+'  and  d.a3='''+@CDXM+''' '

if isnull(@Incident,0)!=0  
 set @StrSql=@StrSql+' and e.Incident='+Cast(@Incident as nvarchar(10)) +' ' 

if isnull(@Status,1001)!=1001  
 set @StrSql=@StrSql+'  and  (('+Cast(@Status as nvarchar(2))+'=1 and a.status=0) or (a.status='+Cast(@Status as nvarchar(10))+')) ' 

if isnull(@lx,0)!=0  
 set @StrSql=@StrSql+' and a.a15='+Cast(@lx as nvarchar(10)) +' '  

set @StrSql=@StrSql+' select * from #T  ' 

if isnull(@JJDept,'')!='' or isnull(@CDDept,'')!='' 
    set @StrSql=@StrSql+ ' where '  

if isnull(@JJDept,'')!=''  
 set @StrSql=@StrSql+'   a4='''+@JJDept+'''  ' 

if isnull(@JJDept,'')!='' and  isnull(@CDDept,'')!=''
    set @StrSql=@StrSql+'   and  '  

if isnull(@CDDept,'')!=''  
 set @StrSql=@StrSql+'   deptName='''+@CDDept+'''  ' 

set @StrSql=@StrSql+' Drop Table #T '
--select @StrSql 
exec(@StrSql)  

1. create table #tab2(admin nvarchar(100));

2. --将拆分数据插入临时表

3. insert into #tab2 EXEC (@sql)

4. --查询

5. select * from #tab2;

6. --删除

7. drop table #tab2

8.