Best Practice / Sample Stored Procedures for Multiple Criteria
The following stored procedure works, but I'm wondering - is there a best practice / pattern for working with this type of activity in SQL Server? Basically I am passing in two different parameters. Based on what is passed for the "criteria" parameter, I run the query with some specific conditions in the where clause - Thanks.
ALTER PROCEDURE [dbo].[Select_Project_Info_By_Value]
(
@value VarChar(50),
@criteria VarChar(50)
)
AS
BEGIN
SET NOCOUNT ON;
if @criteria= 'All'
SELECT some some tables...
FROM dbo.Table1
WHERE (NOT (Status = 'ABC'))
else if @criteria = 'X'
SELECT some some tables...
FROM dbo.Table1
WHERE (Matno LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))
else if @criteria = 'Y'
SELECT some some tables...
FROM dbo.Table1
WHERE (ID LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))
else if @criteria = 'Z'
SELECT some some tables...
FROM dbo.Table1
WHERE (ProDescr LIKE '%' + @value + '%')
else if @criteria = 'A'
SELECT some some tables...
FROM dbo.Table1
WHERE (CustCode LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))
else if @criteria = 'B'
SELECT some some tables...
FROM dbo.Table1
WHERE (ApplName LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))
END
source to share
Perhaps something like this:
SELECT some some tables...
FROM
dbo.Table1
WHERE
(
@criteria= 'All'
AND (NOT (Status = 'ABC'))
)
OR
(
@criteria = 'X'
AND (Matno LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))
)
OR
(
@criteria = 'Y'
AND (ID LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))
)
OR
(
@criteria = 'Z'
AND (ProDescr LIKE '%' + @value + '%')
)
OR
(
@criteria = 'A'
AND (CustCode LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))
)
OR
(
@criteria = 'B'
AND (ApplName LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))
)
source to share
Specific work of optional parameters in cases where the dynamic search terms and conditions were written by Erland Sommarsky. This article details the advantages and disadvantages of each approach (of which there are many). This link is for SQL 2005 and earlier. For 2008 use this link (http://www.sommarskog.se/dyn-search-2008.html)
If I were you, I would skim the article and take the approach, although your query cannot be optimized no matter how you write it. Your queries LIKE '%' + @value + '%'
cannot use the index and you will always scan the table.
In such a situation, you can run into problems with parameterization, since different input parameters can give very different query plans or paths through the code. This proc might be a good candidate for an option WITH RECOMPILE
.
source to share