I am developing a stored procedure that allows users to search by a variety of criteria. Let's say, for the sake of brevitiy, I only search one table with three fields:
ApplicationID INT Identity,
DecisionID INT, -- FK
ResponseID INT -- FK
The stored procedure looks something like:
CREATE PROCEDURE sp_ApplicationList
@DecisionID INT = 0, -- an ID of zero indicates all decisions
@ResponseID INT = 0 -- an ID of zero indicates all response codes
WHERE DecisionID = CASE @DecisionID WHEN 0 THEN DecisionID ELSE @DecisionID END
AND ResponseID = CASE @ResponseID WHEN 0 THEN ResponseID ELSE @ResponseID END
All records in the referenced Type and Decision tables have an ID of 1 or greater. Knowing that I set it up so that if the passed in parameters equal zero all values are returned (at least for that parameter). This allows me to use a CASE statement to search for all matching records.
The problem I have is what if I have Varchar column and I allow users to do exact as well as pattern searches. In one case I have to use LIKE in the other the equal sign. Is there still a way to use a CASE statement (or any other statement to search for either exact matches or wildcards without having to resort to an IF statement?
Any help is appreciated