SQL: Creating search Stored Procedures

Below you'll find a good way to create a search stored procedure. This way you avoid creating different stored procedures to search or filtering the data in-memory.

DECLARE
@ProductName VARCHAR(50),
@CompanyName VARCHAR(50),
@CategoryName VARCHAR(50)
DECLARE
@UnitPrice MONEY,
@UnitsInStock SmallInt

--SET @ProductName = '%Cha%'
SET @CompanyName = '%exotic%'
SET @CategoryName = '%condi%'

SELECT
[Products].[ProductName],
[Suppliers].[CompanyName],
[Categories].[CategoryName],
[Products].[UnitPrice],
[Products].[UnitsInStock]
FROM
[Products]
INNER JOIN [Suppliers] ON [Products].[SupplierId] = [Suppliers].[SupplierId]
INNER JOIN [Categories] ON [Products].[CategoryId] = [Categories].[CategoryId]
WHERE
(@ProductName IS NULL OR [Products].[ProductName] LIKE @ProductName)
AND
(@CompanyName IS NULL OR [Suppliers].[CompanyName] LIKE @CompanyName )
AND
(@CategoryName IS NULL OR [Categories].[CategoryName] LIKE @CategoryName )