>> Help with Stored Procedure

Hi, I'm so new to programming in MS SQL that the paint is still wet behind m
y
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
Many thanks
Jonathan ParminterIf the user doen't select an entry what gets passed in?
if null then
SELECT * FROM vwBusinessSearchResult
where Account like '%' + coalesce(@.Account, '') + '%'
and Business like '%' + coalesce(@.Business, '') + '%'
and Contact like '%' + coalesce(@.Contact, '') + '%'
and BusinessTypeID = coalesce(@.BusinessTypeID, BusinessTypeID)
and ContactID = coalesce(@.ContactID, ContactID)
and ContactTypeID = coalesce(@.ContactTypeID, ContactTypeID)
if empty string and 0 from the IDs then
SELECT * FROM vwBusinessSearchResult
where Account like '%' + @.Account + '%'
and Business like '%' + @.Business + '%'
and Contact like '%' + @.Contact + '%'
and (BusinessTypeID = @.BusinessTypeID or @.BusinessTypeID = 0)
and (ContactID = @.ContactID or @.ContactID = 0)
and (ContactTypeID = @.ContactTypeID or @.ContactTypeID = 0)|||Nigel, excellant
--
Many thanks
Jonathan Parminter
"Nigel Rivett" wrote:

> If the user doen't select an entry what gets passed in?
> if null then
> SELECT * FROM vwBusinessSearchResult
> where Account like '%' + coalesce(@.Account, '') + '%'
> and Business like '%' + coalesce(@.Business, '') + '%'
> and Contact like '%' + coalesce(@.Contact, '') + '%'
> and BusinessTypeID = coalesce(@.BusinessTypeID, BusinessTypeID)
> and ContactID = coalesce(@.ContactID, ContactID)
> and ContactTypeID = coalesce(@.ContactTypeID, ContactTypeID)
> if empty string and 0 from the IDs then
> SELECT * FROM vwBusinessSearchResult
> where Account like '%' + @.Account + '%'
> and Business like '%' + @.Business + '%'
> and Contact like '%' + @.Contact + '%'
> and (BusinessTypeID = @.BusinessTypeID or @.BusinessTypeID = 0)
> and (ContactID = @.ContactID or @.ContactID = 0)
> and (ContactTypeID = @.ContactTypeID or @.ContactTypeID = 0)
>|||Nigel,
Just curious as to the use of coalesce on the @.account, @.business, and
@.contact (given it wasn't the first thing that sprang to mind for me.
Is there any difference performance wise between using coalesce() in
this fashion vs isnull() ?
Glenn|||Jonathan (Jonathan@.discussions.microsoft.com) writes:
> IF @.ContactTypeID > 0
> SET @.SearchOptions = @.SearchOptions + 11
> CASE @.SearchOptions
> WHEN 1 THEN
There is no CASE statement in Transact-SQL. There is a CASE *expression*
which can be used in WHERE clauses, SELECT lists or anywhere else you
can use an expression.
To achieve the same logic, you would have to use bunch of IF ELSE.
For a longer discussion on this particular problem - dynamic search
conditions - I have an article on my web site that discusses a number
of alternatives: http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> 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)