Searching with different criteria in SQL server 2000

From the code calling the SP,set the parameter(s) to null if there is no value.

Have SP parameters set to null and then use COALESCE for the parameters

with the "where" clause  between the criteria as given below.

 

 

CREATE PROCEDURE SearchCustomers
@Cus_Name varchar(30) = NULL,
@Cus_City varchar(30) = NULL,
@Cus_Country varchar(30) =NULL
AS
SELECT Cus_Name,
       Cus_City,
       Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
      Cus_City = COALESCE(@Cus_City,Cus_City) AND
      Cus_Country = COALESCE(@Cus_Country,Cus_Country)