Search multiple columns using multiple parameters

[waynefeltham]

Hi All

My first post here.

I've been struggling for several days now and still can't get this to work!

I have a form with 4 fields which represent the 4 columns in my DB that I want users to search.

AccountNumber
CompanyName
TelephoneNumber
PostCode

I want users to be able to enter search criteria in to as many or as few of the fields as possible. Also, the criteria they enter will not be complete words...so for example, they may enter the first 3 letters of the CompanyName and the last 5 characters of the telephone number.

All of the DB columns used in the search are stored as varchars (the AccountNumber isn't the primary key and most of them begin with 0 hence why it's a varchar).

The problem I'm having is writing the logic that determines if the user has entered something and then structuring the resulting query around this (ie. it would need additional WHERE LIKE clauses).

Can anyone help here?...Please!

Cheers


Wayne

 

[skuhlman]

Create a stored procedure like this:

CREATE PROCEDURE dbo.sp_GetRecords

@AccountNumber varchar(20) = '',
@CompanyName varchar(20) = '',
@TelephoneNumber varchar(20) = '',
@PostCode varchar(20) = ''

AS

declare @FinalSQL nvarchar(4000)
set @FinalSQL = 'SELECT * FROM MyTable '
if @AccountNumber<> '' or @CompanyName <> '' or @TelephoneNumber <> '' or @PostCode <> ''
	set @FinalSQL = @FinalSQL + 'WHERE '
if @AccountNumber <> ''
	begin
		set @FinalSQL = @FinalSQL + 'MyTable.AccountNumber LIKE '''+@AccountNumber+'%'' '
		if @CompanyName <> '' or @TelephoneNumber <> '' or @PostCode <> ''
			set @FinalSQL = @FinalSQL + 'AND '
	end
if @CompanyName <> ''
	begin
		set @FinalSQL = @FinalSQL + 'MyTable.CompanyName LIKE '''+@CompanyName+'%'' '
		if @TelephoneNumber <> '' or @PostCode <> ''
			set @FinalSQL = @FinalSQL + 'AND '
	end
if @TelephoneNumber <> ''
	begin
		set @FinalSQL = @FinalSQL + 'MyTable.TelephoneNumber LIKE '''+@TelephoneNumber+'%'' '
		if @PostCode <> ''
			set @FinalSQL = @FinalSQL + 'AND '
	end
if @PostCode <> ''
	set @FinalSQL = @FinalSQL + 'MyTable.PostCode LIKE '''+@PostCode+'%'' '
set @FinalSQL = 'ORDER BY MyTable.AccountNumber '

exec (@FinalSQL)
GO

Then call the stored procedure with all four parameters:

exec sp_GetRecords null, 'ABC Company', null, '12345'

 

[waynefeltham]

Hi there

thanks for the reply but I think a collegue of mine has solved it and the SQL is a little clearer because i don't use as many IF statements (something I wanted to get away from).

Here it is:

CREATE PROCEDURE CustomerSearch
@AccountNumber varchar(20),
@CompanyName varchar(20),
@PostCode varchar(20),
@TelephoneNumber varchar(20)
AS

SELECT *
FROM Customers C
WHERE
AccountNumber LIKE '%' + @AccountNumber + '%' AND
CompanyName LIKE '%' + @CompanyName + '%' AND
PostCode LIKE '%' + @PostCode + '%' AND
TelephoneNumber LIKE '%' + @TelephoneNumber + '%'
ORDER BY CompanyName

GO

I haven't used default values on the parameters as this was causing some problems when passing in NULL values (it didn't use the default value) and you can't use a NULL value with % wildcards in the LIKE clause.

What do you think?

Cheers


Wayne

 

[skuhlman]

That MAY work IF your data contains NO null values. If you say:

SELECT MyField FROM MyTable WHERE MyField LIKE '%%'

you will not get any records from MyTable that contain NULL in the MyField column. Also the query will technically take longer to run, although it may or may not matter depending upon the size of your data set. The reason for that is that in your method, each of the four fields ALWAYS has to be compared instead of only the fields that the user is interested in.