Customer search stored procedure

I am writing a customer search stored procedure which having three parameters firstname, lastname, fathers name all are varchar

I wrote like below :

CREATE PROCEDURE [dbo].[SearchCustomer] 
(  
  @p_FirstName varchar = NULL
  ,@p_LastName varchar = NULL
  ,@p_FatherFirstName varchar = NULL
)
AS 
BEGIN 
  SET NOCOUNT ON 
  SELECT [CustomerID]
        ,[CustomerTitle]
        ,[FirstName]
        ,[MiddleName]
        ,[LastName]
        ,[FatherFirstName]
        ,[EmailId]
     FROM [Customer] 
       WHERE [FirstName] LIKE COALESCE(@p_FirstName,'%')     
      OR [LastName] LIKE COALESCE @p_LastName,'%')
      OR [FatherFirstName] LIKE COALESCE(@p_FatherFirstName,'%')
  END

I am using optional parameters because if there is no parameter value I need to get full listing without any criteria but when I give just first name I need only those rows which matching that criteria not the rest, but now its listing all

I guess the problem is so开发者_开发百科mewhere near OR

Could you please help?

If i change OR to AND

now if i put first name as 'abc' and last name and father name black ...

i am getting all listing including 'abc' as first name..

i need only the row which having first name as 'abc' not the rest

This will filter rows depending on which parameters were submitted:

CREATE PROCEDURE [dbo].[SearchCustomer] 
(  
  @p_FirstName varchar(50) = NULL
 ,@p_LastName varchar(50) = NULL
 ,@p_FatherFirstName varchar(50) = NULL
)
AS 
BEGIN 

  SET NOCOUNT ON 
  SELECT [CustomerID]
        ,[CustomerTitle]
        ,[FirstName]
        ,[MiddleName]
        ,[LastName]
        ,[FatherFirstName]
        ,[EmailId]
     FROM [Customer] 
   WHERE [FirstName] LIKE COALESCE(@p_FirstName + '%', FirstName)     
   AND [LastName] LIKE COALESCE (@p_LastName + '%', LastName)
   AND [FatherFirstName] LIKE COALESCE(@p_FatherFirstName + '%', FatherFirstName)
  END
GO

Use where clause like the example below .

 WHERE (@p_FirstName=''  or [FirstName] LIKE COALESCE(@p_FirstName,'%'))     
      OR (@p_LastName='' or [LastName] LIKE COALESCE @p_LastName,'%'))
      OR (@p_FatherFirstName='' or [FatherFirstName] LIKE COALESCE(@p_FatherFirstName,'%'))

and set all your parameters '' if they are NULL by using following syntax.

SET @p_FirstName=ISNULL(@p_FirstName,'')

CREATE PROCEDURE [dbo].[SearchCustomer]  
(   
  @p_FirstName varchar = NULL 
  ,@p_LastName varchar = NULL 
  ,@p_FatherFirstName varchar = NULL 
) 
AS  
BEGIN  
  SET NOCOUNT ON  
  SELECT [CustomerID] 
        ,[CustomerTitle] 
        ,[FirstName] 
        ,[MiddleName] 
        ,[LastName] 
        ,[FatherFirstName] 
        ,[EmailId] 
     FROM [Customer]  
       WHERE [FirstName] LIKE COALESCE(@p_FirstName,'%')      
       OR (FirstName is NULL
      AND [LastName] LIKE COALESCE(@p_LastName,'%') 
      AND [FatherFirstName] LIKE COALESCE(@p_FatherFirstName,'%'))
  END 

CREATE PROCEDURE [dbo].[SearchCustomer] 
(  
  @p_FirstName varchar(50) = NULL
  ,@p_LastName varchar(50) = NULL
  ,@p_FatherFirstName varchar(50) = NULL
)
AS 
BEGIN 
SET NOCOUNT ON 

SELECT  [CustomerID]
        ,[CustomerTitle]
        ,[FirstName]
        ,[MiddleName]
        ,[LastName]
        ,[FatherFirstName]
        ,[EmailId]
FROM    [Customer] 
WHERE   ([FirstName] = @p_FirstName OR @p_FirstName IS NULL)
    AND ([LastName] = @p_LastName OR @p_LastName IS NULL)
    AND ([FatherFirstName] = @p_FatherFirstName OR @p_FatherFirstName IS NULL)

END