Help with Stored Procedure

SQL Apprentice Question
Hi, I'm so new to programming in MS SQL that the paint is still wet behind my
ears...

Using sql 2000 and vb.net (I normally develop in MS Access)

I have a search form set up so that users can enter any combination of the
following search criteria:
User types all or part business name,
User types all or part account name,
User selects from a ComboBox a business type
User types all or part contact name,
User selects from a ComboBox a contact type

I want to have a stored procedure that can handle any combinations of data
entry. The following is my guess for structure and it does not compile...

CREATE PROCEDURE GetSearchResult
@Account nvarchar(10),
@Business nvarchar(100),
@BusinessTypeID Int,
@ContactID Int,
@Contact nvarchar(100),
@ContactTypeID Int
DECLARE @SearchOptions Int
DECLARE @ErrorStatus Int OUTPUT
AS
IF LEN(@Business)>0
SET @SearchOptions = 1
IF LEN(@Account) > 0
SET @SearchOptions = @SearchOptions + 2
IF @BusinessTypeID > 0
SET @SearchOptions = @SearchOptions + 4
IF LEN(@Contact) > 0
SET @SearchOptions = 10
IF @ContactTypeID > 0
SET @SearchOptions = @SearchOptions + 11
CASE @SearchOptions
WHEN 1 THEN
-- Only search for Business name
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bBusiness LIKE '%' + @Business + '%'
END
WHEN 2 THEN
-- Only search for Account name
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bAccount LIKE '%' + @Account + '%'
END
WHEN 3 THEN
-- Search for Business name and Account
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bBusiness LIKE '%' + @Business + '%'
AND bAccount LIKE '%' + @Account + '%'
END
WHEN 4 THEN
-- Only search for Business type id
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bID Exists In(SELECT bbtBusinessID
FROM tblBusinessBusinessType
WHERE bbtBusinessTypeID = @BusinessTypeID)
END
WHEN 5 THEN
-- Search for Business name and Business type id
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bBusiness LIKE '%' + @Business + '%'
AND bID Exists In(SELECT bbtBusinessID
FROM tblBusinessBusinessType
WHERE bbtBusinessTypeID = @BusinessTypeID)
END
WHEN 6 THEN
-- Search for Account and Business type id
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bAccount LIKE '%' + @Account + '%'
AND bID Exists In(SELECT bbtBusinessID
FROM tblBusinessBusinessType
WHERE bbtBusinessTypeID = @BusinessTypeID)
END
WHEN 7 THEN
-- Search for Business name, Account and Business type id
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bBusiness LIKE '%' + @Business + '%'
AND bAccount LIKE '%' + @Account + '%'
AND bID Exists In(SELECT bbtBusinessID
FROM tblBusinessBusinessType
WHERE bbtBusinessTypeID = @BusinessTypeID)
END
WHEN 10 THEN
-- Only Search for Contact name
BEGIN
SELECT * FROM vwBusinessContactSearchResult
WHERE cFullName LIKE '%' + @Contact + '%'
END
WHEN 11 THEN
-- Only search for Contact type id
BEGIN
SELECT * FROM vwBusinessContactSearchResult
WHERE cID Exists In(SELECT cctContactID
FROM tblContactContactType
WHERE cctContactTypeID = @ContactTypeID)
END
WHEN 21 THEN
-- Search for Contact name name Contact type id
BEGIN
SELECT * FROM vwBusinessContactSearchResult
WHERE cFullName LIKE '%' + @Contact + '%'
AND cID Exists In(SELECT cctContactID
FROM tblContactContactType
WHERE cctContactTypeID = @ContactTypeID)
END
END
GO
GRANT EXECUTE ON GetSearchResult TO PUBLIC
GO

Can you please give me advice regarding my approach to facilitating a search
form and corrections for more stored procedure


Celko Answers
>> Is there any difference performance wise between using coalesce() in
this fashion vs isnull() ? <<

COALESCE () is Standard SQL and takes a list of parameters. It is a
member fo the CASE expression family.

COALESCE correctly promotes its arguments to the highest data type in
the expression:

13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5

The proprietary ISNULL() uses the first data type and gets things wrong

13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6

You would need to write:

13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)