Flexible search for customer SQL

Question

Person

| p_id | n_name | l_name | address | city | state | zip |

Customer

| p_id | reward_points| balance |

Person_PhoneNum

| ppn_id | p_id | number |

Main issue is that I want to attempt making a Retrieve Stored Procedure that can search by any of Person's fields as well as phone number or p_id BUT I want it to be able to handle NULL values from the parameters. Here is the Stored Procedure below:

CREATE PROCEDURE RetrieveCust(
@p_id AS varchar(50),
@f_name AS varchar(50),
@l_name AS varchar(50),
@address AS varchar(50),
@city AS varchar(50),
@state AS varchar(50),
@zip AS varchar(50),
@number AS varchar(50))

AS
BEGIN

END

I understand that I need to join the tables in order to match results but I don't know what I could do to handle NULL values. Any help would be amazing!

Answer 1

your where statement could be something like this

where (f_name = @f_name or @f_name is null)

Answer 2

Any NULL in a parameter should match any value in the tables. Whenever you compare a parameter to a table field OR that comparison with a test for a null parameter:

( @f_name = f_name ) or ( @f_name is null )

then AND all those comparisons together to make up your retrieval.

The comparison against the phone number when phone number is null will result in more that one row if they have more than one phone number so Select DISTINCT on p_id.

What does Customer have to do with the query? You're not selecting on any field in that table and you don't appear to be returning any values from the procedure.