create table dbo.tab1
([City] varchar(100),
[Office] varchar(100),
[Product] varchar(100)
)
insert into dbo.tab1
select 'КАРАГАНДА',NULL,NULL
union all
select 'Москва','Офис1','сырок'
union all
select 'Москва','Офис1','виноград сушеный изюм'
union all
select 'Москва','Офис2','огурцы соленые'
union all
select 'Москва','Офис1','халва'
union all
select 'Москва','Офис1','халва'
union all
select 'Москва','Офис10','инжир'
declare
@s varchar(max),
@pCity varchar(100),
@pOffice varchar(100),
@pProduct varchar(100)
set @s = 'select * from dbo.tab1 '
--set @pCity = 'КАРА'
--set @pOffice = 'Офис1'
--set @pProduct = 'хал'
if not ((@pCity is NULL) and (@pOffice is NULL) and (@pProduct is NULL))
set @s = @s + ' where '+' [City] like '''+isnull('%'+@pCity+'%','%')+''''+' and [Office] like '''+isnull('%'+@pOffice+'%','%')+''''+' and [Product] like '''+isnull('%'+@pProduct+'%','%')+''''
print @s
И что получается
--первый параметр
select * from dbo.tab1 where [City] like '%КАРА%' and [Office] like '%' and [Product] like '%'
--второй параметр
select * from dbo.tab1 where [City] like '%' and [Office] like '%Офис1%' and [Product] like '%'
--третий параметр
select * from dbo.tab1 where [City] like '%' and [Office] like '%' and [Product] like '%хал%'
--ну и с двумя параметрами
select * from dbo.tab1 where [City] like '%КАРА%' and [Office] like '%Офис10%' and [Product] like '%'