CREATE proc test
@faid nvarchar(20)
@fatypeid nvarchar(20)
AS
begin
select faid,max(item)as item
into #table1
from fa_valuedtl group by faid
select t2.*
into #table2
from #table1 t1,fa_valuedtl t2 where t1.item=t2.item and t1.faid=t2.faid
select t1.*,t2.value
from fa_main t1
left join #table2 t2 on t1.faid=t2.faid
where t1.faid=@faid and t1.fatypeid=@fatypeid
end 这段如果传入参数,可以查到所需要的。可是我如果传入的参数为空,那where中的那个参数条件就去掉,该如何改?
求指教~~~~~~~
@faid nvarchar(20)
@fatypeid nvarchar(20)
AS
begin
select faid,max(item)as item
into #table1
from fa_valuedtl group by faid
select t2.*
into #table2
from #table1 t1,fa_valuedtl t2 where t1.item=t2.item and t1.faid=t2.faid
select t1.*,t2.value
from fa_main t1
left join #table2 t2 on t1.faid=t2.faid
where t1.faid=@faid and t1.fatypeid=@fatypeid
end 这段如果传入参数,可以查到所需要的。可是我如果传入的参数为空,那where中的那个参数条件就去掉,该如何改?
求指教~~~~~~~
where t1.faid=isnull(@faid,t1.faid) and t1.fatypeid=isnull(@fatypeid,t1.fatypeid)
if isnull(@faid,'') = '' set @faid = null