How can I turn the below basic procedure into one that will accept the various options (below) and return results. Obviously, as it stands now, if I leave a variable blank it will not return results, same if I set it to null.

@searchterm must have at lease one character or word.
@Category must accept an integer or return all records if left blank.
@Subcategory same as above.
@Submitter same as above.

Thanks in advance for your help!


CREATE PROCEDURE SearchDocument

(
@SearchTerm char (200),
@Category int,
@Subcategory int,
@SubmitterID int
)

as


SELECT dbo.tblDocument.DocumentID,
       dbo.tblDocument.Title,
       dbo.tblSubmitter.SubmitterName

FROM dbo.tblDocument join dbo.tblSubmitter
      on (tbldocument.submitterid = tblsubmitter.submitterid)

where freetext (document, @searchterm) and
        (dbo.tblDocument.SubCategoryID = @subcategory or
        dbo.tblDocument.CategoryID = @category or
        dbo.tblDocument.SubmitterID = @submitterID)

Use what I've called the "coalesce trick". Use a parameter = NULL as a "don't care" value.

Your WHERE expression then becomes (e.g.):
When the parameter @Category is NULL, then the coalesce function returns the value of the 'CategoryID' column. Comparing it to itself is always true, so this is a No-op, i.e "don't care" what the 'Category' value is. When @Category is not NULL, then the compare is to the parameter value.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hello, this doesnt seem to work for me (With regards to Coalesce)

I am trying to return the customer row, which matches the text. Their are 3 parameters, and these should be optional. Here is my code, can someone point me in the right direction please

ALTER PROCEDURE CustomerSearch
(
@CompanyName varchar(50) = null,
@PostCode varchar(50) = null,
@ContactName varchar(50) = null
)
AS
BEGIN
SET NOCOUNT ON
Select * FROM Customers WITH (NOLOCK)
WHERE CompanyName LIKE COALESCE(@CompanyName + '%', Customers.CompanyName) OR (@CompanyName = null)
AND PostCode LIKE COALESCE(@PostCode + '%', Customers.PostCode) OR (PostCode = null)
AND ContactName LIKE COALESCE(@ContactName + '%', Customers.ContactName) OR (@ContactName = null);


End
Return


Many thanks