Philip Ng Malaysia's Blog

Syntax to allow a store procedure to accept wildcard '%' key in by user from the GUI to search the data in the database.

The following samples working in Microsoft SQL server 2000 on Northwind database

If you want to allow to search all record, just pass in a empty string from the User interface to the store procedure.

Start of samples
---------------------------------------------

declare @criteria as varchar(50)

set @criteria = ''
select * from customers WITH (NOLOCK)
where (CompanyName like @criteria or @criteria = '' )

set @criteria = 'Al%'
select * from customers WITH (NOLOCK)
where (CompanyName like @criteria or @criteria = '' )

set @criteria = '%orn'
select * from customers WITH (NOLOCK)
where (CompanyName like @criteria or @criteria = '' )

set @criteria = '%oren%'
select * from customers WITH (NOLOCK)
where (CompanyName like @criteria or @criteria = '' )


-----------------------------------------------------
End of samples

Special thanks to Leion Cheong for providing the above information .

------------------------------------------------------

Extra Information on SQL statement:

When performing a select statement, make sure you have "WITH (NOLOCK)" at the end of statement . Example as follow.

Start of Sample
----------------------------
select CompanyName from customers WITH (NOLOCK)
----------------------------
End of Sample

Else if it is not place, the whole record might be lock. Do not use select * from a table as there is more processing to be done and hence reduce performance. Only put in the field you needed.

Special thanks for Patrick Yong for the above information.
---------------------------------------------------------