Search Query in SQL

I wrote SQL query for search,where I am passing some parameters and based on that parameters looking for the search results.something going wrong with my search query it's not working properly.

Below is my sql query.

ALTER  PROCEDURE [dbo].[Nop_CustomerLoadAll]
(
	@StartTime	datetime = NULL,
	@EndTime	datetime = NULL,
	@Email		nvarchar(200),
	@Username	nvarchar(200),	
	@City		nvarchar(200),
	@Name		nvarchar(200),
	@Company nvarchar(200),
	@ZipPostalCode nvarchar(600),
	@DontLoadGuestCustomers	bit = 0,
	@PageIndex			int = 0, 
	@PageSize			int = 2147483644,
	@TotalRecords		int = null OUTPUT,
	@StoreID	uniqueidentifier
)
AS
BEGIN

	SET @Email = isnull(@Email, '')
	SET @Email = '%' + rtrim(ltrim(@Email)) + '%'

	SET @Username = isnull(@Username, '')
	SET @Username = '%' + rtrim(ltrim(@Username)) + '%'

	SET @ZipPostalCode = isnull(@ZipPostalCode, '')

	SET @Company = isnull(@Company, '')
	SET @Company = '%' + rtrim(ltrim(@Company)) + '%'
	
	
	if @City IS NULL
	begin
	SET @City = ''
	end
	else
	begin
	SET @City = '%' + rtrim(ltrim(@City)) + '%'
	end
	
   if @Name IS NULL
	begin
	SET @Name =''
	end
	else
	begin
	SET @Name = '%' + rtrim(ltrim(@Name)) + '%'
	end
	--SET @Name = isnull(@Name, '')
	--SET @Name = '%' + rtrim(ltrim(@Name)) + '%'

	
	
	--paging
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @RowsToReturn int
	DECLARE @TotalThreads int
	
	SET @RowsToReturn = @PageSize * (@PageIndex + 1)	
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1
	
	CREATE TABLE #PageIndex 
	(
		IndexID int IDENTITY (1, 1) NOT NULL,
		CustomerID int NOT NULL,
		RegistrationDate datetime NOT NULL,	
		StoreID uniqueidentifier
	)

	INSERT INTO #PageIndex (CustomerID, RegistrationDate, StoreID)
	SELECT DISTINCT
		c.CustomerID, c.RegistrationDate,c.StoreID
											  
	FROM [Nop_Customer] c  with (NOLOCK) 
	INNER JOIN Nop_CustomerAttribute ca ON c.CustomerID = ca.CustomerId 
	WHERE 
		(@StartTime is NULL or DATEDIFF(day, @StartTime, c.RegistrationDate) >= 0) and
		(@EndTime is NULL or DATEDIFF(day, @EndTime, c.RegistrationDate) <= 0) and 
		(patindex(@Email, isnull(c.Email, '')) > 0) AND
		(patindex(@Username, isnull(c.Username, '')) > 0)	
	  AND 			
		(@DontLoadGuestCustomers = 0 or (c.IsGuest = 0)) AND 
		c.deleted = 0 AND 
		c.StoreID = @StoreID
	order by c.RegistrationDate desc 

	CREATE TABLE #PageIndex2
	(
		IndexID int IDENTITY (1, 1) NOT NULL,
		CustomerID int NOT NULL,
		RegistrationDate datetime NOT NULL,		
		City varchar(300),	
		FirstName varchar(500),
		LastName varchar(500),
		ZipPostalCode varchar(600),
		Company varchar(500),
		StoreID uniqueidentifier
	)
	INSERT INTO #PageIndex2 (CustomerID, RegistrationDate, StoreID,City,FirstName,LastName,ZipPostalCode,Company)
	SELECT 	c.CustomerID, c.RegistrationDate,c.StoreID,Ca.Value,'','','',''
		
			FROM [Nop_Customer] c  with (NOLOCK) 
	INNER JOIN Nop_CustomerAttribute ca ON c.CustomerID = ca.CustomerId 						
	where ca.[key] = 'City' and  ca.Value <> ''


	Insert into  #PageIndex2 (CustomerID, RegistrationDate, StoreID,City,FirstName,LastName,ZipPostalCode,Company)
	SELECT 	c.CustomerID, c.RegistrationDate,c.StoreID,'',Ca.Value,'','',''
			FROM [Nop_Customer] c  with (NOLOCK) 
	INNER JOIN Nop_CustomerAttribute ca ON c.CustomerID = ca.CustomerId 		
	where (ca.[key] = 'FirstName' )   and  ca.Value <> '' and c.CustomerID not in (select CustomerID from #PageIndex2)	
	
	update	 PI2
	 SET FirstName = ca.Value
	 from #PageIndex2 PI2
	  INNER JOIN Nop_CustomerAttribute ca
	 ON PI2.CustomerID = ca.CustomerId 
	 where 	(ca.[key] = 'FirstName' )  and  ca.Value <> ''

	 Insert into  #PageIndex2 (CustomerID, RegistrationDate, StoreID,City,FirstName,LastName,ZipPostalCode,Company)
	SELECT 	c.CustomerID, c.RegistrationDate,c.StoreID,'','',Ca.Value,'',''		
			FROM [Nop_Customer] c  with (NOLOCK) 
	INNER JOIN Nop_CustomerAttribute ca ON c.CustomerID = ca.CustomerId 		
	where (ca.[key] = 'LastName' )   and  ca.Value <> '' and c.CustomerID not in (select CustomerID from #PageIndex2)	
	
	 update	 PI2
	 SET LastName = ca.Value
	 from #PageIndex2 PI2
	 INNER JOIN Nop_CustomerAttribute ca
	 ON PI2.CustomerID = ca.CustomerId 
	 where 	(ca.[key] = 'LastName' )  and  ca.Value <> ''

    Insert into  #PageIndex2 (CustomerID, RegistrationDate, StoreID,City,FirstName,LastName,ZipPostalCode,Company)
	SELECT 	c.CustomerID, c.RegistrationDate,c.StoreID,'','','',Ca.Value,''	
			FROM [Nop_Customer] c  with (NOLOCK) 
	INNER JOIN Nop_CustomerAttribute ca ON c.CustomerID = ca.CustomerId 		
	where (ca.[key] = 'ZipPostalCode' )   and  ca.Value <> '' and c.CustomerID not in (select CustomerID from #PageIndex2)	
	
	update	 PI2
	 SET ZipPostalCode = ca.Value
	 from #PageIndex2 PI2
	  INNER JOIN Nop_CustomerAttribute ca
	 ON PI2.CustomerID = ca.CustomerId 
	 where 	(ca.[key] = 'ZipPostalCode' )  and  ca.Value <> ''

	  Insert into  #PageIndex2 (CustomerID, RegistrationDate, StoreID,City,FirstName,LastName,ZipPostalCode,Company)
	SELECT 	c.CustomerID, c.RegistrationDate,c.StoreID,'','','','',Ca.Value
			FROM [Nop_Customer] c  with (NOLOCK) 
	INNER JOIN Nop_CustomerAttribute ca ON c.CustomerID = ca.CustomerId 		
	where (ca.[key] = 'Company' )   and  ca.Value <> '' and c.CustomerID not in (select CustomerID from #PageIndex2)	
	
	update	 PI2
	 SET Company = ca.Value
	 from #PageIndex2 PI2
	  INNER JOIN Nop_CustomerAttribute ca
	 ON PI2.CustomerID = ca.CustomerId 
	 where 	(ca.[key] = 'Company' )  and  ca.Value <> ''
	 

	select CustomerInfo.*,pi2.* from
	 (
	 SELECT  
	c.*, [pi].IndexID, substring
                             ((SELECT        (', ' + CR.Name)
                                 FROM            Nop_Customer_CustomerRole_Mapping CPM INNER JOIN
                                                          Nop_CustomerRole CR ON CR.CustomerRoleID = CPM.CustomerRoleID
														  and CR.StoreID = CPM.StoreID
                                 WHERE        CPM.CustomerID = C.CustomerID FOR XML PATH('')), 2, 999999) AS 'RoleName'
	FROM
		#PageIndex [pi]
		INNER JOIN [Nop_Customer] c on c.CustomerID = [pi].CustomerID AND c.StoreID=[pi].StoreID			
	WHERE
		[pi].IndexID > @PageLowerBound AND 
		[pi].IndexID < @PageUpperBound AND 
		[pi].StoreID=@StoreID	
		)   CustomerInfo 
		
		left join  #PageIndex2 pi2 on CustomerInfo.CustomerID = [pi2].CustomerID AND CustomerInfo.StoreID=[pi2].StoreID	

	--	select * from #PageIndex2
		
WHERE 

	
  ((pi2.FirstName like @Name OR @Name = '' or pi2.FirstName IS NULL) or((pi2.LastName like @Name OR @Name = '' or pi2.LastName IS NULL)))
	and
 (pi2.City like @City OR @City = '' or pi2.City IS NULL)
 and
  (pi2.ZipPostalCode = @ZipPostalCode OR @ZipPostalCode = '' or pi2.ZipPostalCode  IS NULL)
  and 
  (pi2.Company like @Company OR @Company = '' or  pi2.Company IS NULL)

  or CustomerInfo.Email like @Email
  	ORDER BY
		CustomerInfo.IndexID
 
 			SET @TotalRecords = @@rowcount	
	SET ROWCOUNT @RowsToReturn
	
--	ORDER BY
--		CustomerInfo.IndexID

	SET ROWCOUNT 0

	DROP TABLE #PageIndex
	drop table #PageIndex2
	
END

Select allOpen in new window

I think it's going wrong at the below  condition area.

  ((pi2.FirstName like @Name OR @Name = '' or pi2.FirstName IS NULL) or((pi2.LastName like @Name OR @Name = '' or pi2.LastName IS NULL)))
      and
 (pi2.City like @City OR @City = '' or pi2.City IS NULL)
 and
  (pi2.ZipPostalCode = @ZipPostalCode OR @ZipPostalCode = '' or pi2.ZipPostalCode  IS NULL)
  and
  (pi2.Company like @Company OR @Company = '' or  pi2.Company IS NULL) .


when some of parameters are empty or NULL it's not giving me proper result.

Please help me out.I already wasted so much time to resolve this query.