complex search logic

Question

complex search logic

kcj

Mr or Mrs. 500

Points: 540
More actions
December 19, 2008 at 7:51 pm

#71117

The following stored procedure should query the table for consumers matching all of the supplied parameters that have value. Not all of the parameters will have value. In fact, its possible that no parameters have values except for Provider_ID and Network_ID. In the case that none of the other parameters have value, the stored procedure should return an empty set. At least one of the parameters must be populated.
The stored procedure may be provided a Full parameter such as a first name = "Matt" in which case the select statement should match both "Matt", "MATT", "matt", "Matthew", etc.
The stored procedure may receive partial data such as Last_Name = "G" and First_Name = "M" which would return "Matthew Gregory", "Mark Gibson", "Melissa Green", etc.
The user is not limited to the combination of parameters that can be populated. For example, they can only supply Birth_dt and Internal_Id… or medical_id only … or SSN only… etc,,,
All of them could be so the more data that is supplied, it is expected the more restricted the result set.
How can I write this complex matching logic in T-sql where clause?
-- =============================================
CREATE PROCEDURE usp_Consumer_Search
@Provider_ID int,
@Network_ID int,
@Consumer_Last_Name varchar(30),
@Consumer_First_Name varchar(25),
@Consumer_Birth_Date varchar(10),
@Internal_ID varchar(25),
@Consumer_Medical_ID varchar(12),
@Consumer_SSN varchar(11)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT *
FROM Consumer_Reserve B
INNER JOIN dbo.Provider A
ON A.Provider_ID = B.Provider_ID
WHERE
Provider_ID = @provider_id and
Network_Id = @Network_ID and
??????????????????
Order by Consumer_Last_Name, Consumer_First_Name,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Read this - http://www.sommarskog.se/dyn-search.html

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

Also asked and answered here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116600

N 56°04'39.16"
E 12°55'05.25"

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 3

Hi Jung,

I think GilaMonster has given the exellent sollution. But I would like to add something more here.

You also user CASE statement inplace of OR condition.

For example...

Consumer_SSN = (CASE WHEN @Consumer_SSN IS NOT NULL THEN @Consumer_SSN ELSE Consumer_SSN END)

and for String match you can use

Last_Name like (CASE WHEN @Last_Name IS NOT NULL THEN @Last_Name + '%' ELSE Last_Name END)

Best Regards

Nitin Patel

http://www.EnlinkURL.com

Regards,
Nitin

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

Last_Name like COALESCE(@Last_Name, '') + '%'

N 56°04'39.16"
E 12°55'05.25"