Using multiple fields to enable a search

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using multiple fields to enable a search

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-18 : 21:12:28

David writes "What I'd like to do is give the user the capability of choosing records using multiple and's and or's basically something like this

select * from table where (name1= tbl.name1 and unit1= tbl.unit1) OR (name2 = tbl.name2 and unit1= tbl.unit1) OR ...etc

how can i do this?"

JamesH
Posting Yak Master

149 Posts

Posted - 2002-02-19 : 12:34:12

Here's an example of what I think you're trying to do. It can get very complicated and I would suggest possibly having your app use seperate procs depending on the application and requirements instead of creating a monster proc like this.

create proc sp_GetSalesCheck(

@ReportType varchar(20) = 'Bookings',
-- Report Time Frame Parameters (default to fiscal 2001)
@ReportBeginDate varchar(8) = '20000326',
@ReportEndDate varchar(8) = '20010331',

@Period01 varchar(6) = '200101',
@Period02 varchar(6) = '200102',
@Period03 varchar(6) = '200103',
@Period04 varchar(6) = '200104',
@Period05 varchar(6) = '200105',
@Period06 varchar(6) = '200106',
@Period07 varchar(6) = '200107',
@Period08 varchar(6) = '200108',
@Period09 varchar(6) = '200109',
@Period10 varchar(6) = '200110',
@Period11 varchar(6) = '200111',
@Period12 varchar(6) = '200112',
-- Material Master Fields
@vcMaterial varchar(25)= '',
@vcMaterialFamily varchar(50)= '',
@vcDivisionDescription varchar(50)= '',
@vcProductLine varchar(25)= '',
@vcProductManager varchar(50)= '',
@vcMaterialPlant varchar(25)= '',
@vcMaterialGroupDescription varchar(50)= '',
@vcMaterialType varchar(25)= '',
@vcMaterialTechnology varchar(50)= '',
@vcMaterialCatalogCatagory varchar(50)= '',
@vcMaterialPackageType varchar(25)= '',
@vcPlannerName varchar(50)= '',
@vcApplicationDescription varchar(50)= '',
@vcApplicationEngineer varchar(50)= '',
@vcProductEngineer varchar(50)= '',
@vcMarketingManager varchar(50)= '',
@vcCustomerQualityEngineer varchar(50)= '',
-- Customer Master fields
@vcCustomerID varchar(15)= '',
@vcCustomerDescription varchar(50)= '',
@vcForecastParentID varchar(50)= '',
@vcForecastParentDescription varchar(50)= '',
@vcCustomerHeirarchyID varchar(50)= '',
@vcCustomerHeirarchyDescription varchar(50)= '',
@vcSalesDivision varchar(50)= '',
@vcSalesDistrictDescription varchar(50)= '',
@vcSalesOfficeDescription varchar(50)= '',
@vcSalesTerritoryDescription varchar(50)= '',
@vcCustomerState varchar(50)= '',
@vcCustomerCountry varchar(50)= '',
@vcCustomerCity varchar(50)= '',
@vcCustomerPostalCode varchar(50)= '',
@vcDistributionChannel varchar(50)= '',
@vcSalesRepFirm varchar(50)= '',
@vcAccountGroup varchar(50)= '',
-- Filters
@vcFilters varchar(1000)=''
) AS

declare @sql varchar(8000)

set nocount on
set @sql= ' SELECT

''SalesQuantity'' = sum(case when S.vcType = ''Shipments'' then S.iQuantity else 0 end),
''BookQuantity'' = sum(case when S.vcType = ''Bookings'' then S.iQuantity else 0 end),
''BLCRDQuantity'' = sum(case when S.vcType = ''BLCRD'' then S.iQuantity else 0 end),
''BLFCDQuantity'' = sum(case when S.vcType = ''BLFCD'' then S.iQuantity else 0 end),
''IHCRDQuantity'' = sum(case when S.vcType = ''IHCRD'' then S.iQuantity else 0 end),
''IHFCDQuantity'' = sum(case when S.vcType = ''IHFCD'' then S.iQuantity else 0 end)
FROM
tActuals S (nolock)
left outer join tCustomerMaster C (nolock) on S.vcCustKey = C.vcCustKey
left outer join tMaterialMaster M (nolock) on S.vcMaterial = M.vcMaterial
left outer join tFiscalCalendar F (nolock) on S.CreateDate = F.vcDate
WHERE
S.CreateDate >= ''' + @ReportBeginDate + ''' and
S.CreateDate <= ''' + @ReportEndDate + ''''

if @vcMaterial <> ''
begin
set @sql = @sql + ' and M.vcMaterial = ''' + @vcMaterial + ''''
end
if @vcMaterialFamily <> ''
begin
set @sql = @sql + ' and M.vcMaterialFamily = ''' + @vcMaterialFamily + ''''
end
if @vcDivisionDescription <> ''
begin
set @sql = @sql + ' and M.vcDivisionDescription = ''' + @vcDivisionDescription + ''''
end
if @vcProductLine <> ''
begin
set @sql = @sql + ' and M.vcProductLine = ''' + @vcProductLine + ''''
end
if @vcProductManager <> ''
begin
set @sql = @sql + ' and M.vcProductManager = ''' + @vcProductManager + ''''
end
if @vcMaterialPlant <> ''
begin
set @sql = @sql + ' and M.vcMaterialPlant = ''' + @vcMaterialPlant + ''''
end
if @vcMaterialGroupDescription <> ''
begin
set @sql = @sql + ' and M.vcMaterialGroupDescription = ''' + @vcMaterialGroupDescription + ''''
end
if @vcMaterialType <> ''
begin
set @sql = @sql + ' and M.vcMaterialType = ''' + @vcMaterialType + ''''
end
if @vcMaterialTechnology <> ''
begin
set @sql = @sql + ' and M.vcMaterialTechnology = ''' + @vcMaterialTechnology + ''''
end
if @vcMaterialCatalogCatagory <> ''
begin
set @sql = @sql + ' and M.vcMaterialCatalogCatagory = ''' + @vcMaterialCatalogCatagory + ''''
end
if @vcMaterialPackageType <> ''
begin
set @sql = @sql + ' and M.vcMaterialPackageType = ''' + @vcMaterialPackageType + ''''
end
if @vcPlannerName <> ''
begin
set @sql = @sql + ' and M.vcPlannerName = ''' + @vcPlannerName + ''''
end
if @vcApplicationDescription <> ''
begin
set @sql = @sql + ' and M.vcApplicationDescription = ''' + @vcApplicationDescription + ''''
end
if @vcApplicationEngineer <> ''
begin
set @sql = @sql + ' and M.vcApplicationEngineer = ''' + @vcApplicationEngineer + ''''
end
if @vcProductEngineer <> ''
begin
set @sql = @sql + ' and M.vcProductEngineer = ''' + @vcProductEngineer + ''''
end
if @vcMarketingManager <> ''
begin
set @sql = @sql + ' and M.vcMarketingManager = ''' + @vcMarketingManager + ''''
end
if @vcCustomerQualityEngineer <> ''
begin
set @sql = @sql + ' and M.vcCustomerQualityEngineer = ''' + @vcCustomerQualityEngineer + ''''
end

-- **** Customer Master Filters ****

if @vcCustomerID <> ''
begin
set @sql = @sql + ' and C.vcCustomerID = ''' + @vcCustomerID + ''''
end
if @vcCustomerDescription <> ''
begin
set @sql = @sql + ' and C.vcCustomerDescription = ''' + @vcCustomerDescription + ''''
end
if @vcForecastParentID <> ''
begin
set @sql = @sql + ' and C.vcForecastParentID = ''' + @vcForecastParentID + ''''
end
if @vcForecastParentDescription <> ''
begin
set @sql = @sql + ' and C.vcForecastParentDescription = ''' + @vcForecastParentDescription + ''''
end
if @vcCustomerHeirarchyID <> ''
begin
set @sql = @sql + ' and C.vcCustomerHeirarchyID = ''' + @vcCustomerHeirarchyID + ''''
end
if @vcCustomerHeirarchyDescription <> ''
begin
set @sql = @sql + ' and C.vcCustomerHeirarchyDescription = ''' + @vcCustomerHeirarchyDescription + ''''
end
if @vcSalesDivision <> ''
begin
set @sql = @sql + ' and C.vcSalesDivision = ''' + @vcSalesDivision + ''''
end
if @vcSalesDistrictDescription <> ''
begin
set @sql = @sql + ' and C.vcSalesDistrictDescription = ''' + @vcSalesDistrictDescription + ''''
end
if @vcSalesOfficeDescription <> ''
begin
set @sql = @sql + ' and C.vcSalesOfficeDescription = ''' + @vcSalesOfficeDescription + ''''
end
if @vcSalesTerritoryDescription <> ''
begin
set @sql = @sql + ' and C.vcSalesTerritoryDescription = ''' + @vcSalesTerritoryDescription + ''''
end
if @vcCustomerState <> ''
begin
set @sql = @sql + ' and C.vcCustomerState = ''' + @vcCustomerState + ''''
end
if @vcCustomerCountry <> ''
begin
set @sql = @sql + ' and C.vcCustomerCountry = ''' + @vcCustomerCountry + ''''
end
if @vcCustomerCity <> ''
begin
set @sql = @sql + ' and C.vcCustomerCity = ''' + @vcCustomerCity + ''''
end
if @vcCustomerPostalCode <> ''
begin
set @sql = @sql + ' and C.vcCustomerPostalCode = ''' + @vcCustomerPostalCode + ''''
end
if @vcDistributionChannel <> ''
begin
set @sql = @sql + ' and C.vcDistributionChannel = ''' + @vcDistributionChannel + ''''
end
if @vcSalesRepFirm <> ''
begin
set @sql = @sql + ' and C.vcSalesRepFirm = ''' + @vcSalesRepFirm + ''''
end
if @vcAccountGroup <> ''
begin
set @sql = @sql + ' and C.vcAccountGroup = ''' + @vcAccountGroup + ''''
end

--*********************** Filters *******************
if @vcFilters <> ''
begin
set @sql = @sql + ' and ' + @vcFilters
end

exec (@sql)
--select left(@sql,256),right(@sql,len(@SQL)-256)
GO

JamesH

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-19 : 14:03:18

If you don't need to perform a pattern search with LIKE,
you may try using COALESCE to avoid building dynamic SQL.

something like this
SELECT *
FROM tblTest
WHERE field1=COALESCE(@field1, field1)
AND field2=COALESCE(@field2, field2)

Try searching developers forum on COALESCE or robvolk - it has a few good examples of how dynamic SQL or COALESCE work.

helena

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources