Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2008-12-19 : 21:44:55
|
| 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, |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-20 : 04:49:14
|
Use LIKE.WHERE FirstName LIKE @ParamFirstName +'%'
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
|
|
|
|
|
complex matching logic