两表整合，兼多表关联查询

declare @sqlstr varchar(max), @querywhere varchar(max)
--set @querywhere='where BarCode like ''%9%'''
set @querywhere='where BarCode =''9'''
set @sqlstr='
select distinct 0 as ttype,s.Id,s.StoreNo as tNo,s.StoreName as tName,s.Provinces,s.City,s.Region
from ST_Store s
left join ST_StoreShelf as ss on (ss.S_Id=s.Id)
left join ST_ShelfReserve as sr on (sr.S_Id=ss.Id)
left join CO_Commodity as c on(sr.C_Id=c.Id),ST_Depot d '+@querywhere+'
union
select distinct 1 as ttype,d.Id,d.DepotNo as tNo,d.DepotName as tName,d.Provinces,d.City,d.Region
from ST_Depot d
left join ST_DepotReserve dr on (dr.D_Id=d.Id)
left join CO_Commodity c on (c.Id=dr.C_Id),ST_Store s '+@querywhere

exec(@sqlstr)