Partial serach query

CREATE PROCEDURE [dbo].[PMP_GetOrganizationSearchInfowithrows_BGCM_BestMatch]  
--1,20,' ',' and c.Full_Name like ''co%''' , ' and c.Full_Name like ''%co%'' and c.Full_Name not like ''co%'''                        
@startRowIndex int ,                                 
@maximumRows int,                                                
@whereClause VARCHAR(max), 
@ExactSearchClause VARCHAR(1000), 
@BestSearchClause VARCHAR(1000), 
@PartialSearchClause VARCHAR(1000)                                                     
AS                                                                       
BEGIN                                   
           
DECLARE @LowerBound INT                                 
DECLARE @UpperBound INT                                 
SET @LowerBound = (@maximumRows * (@startRowIndex - 1) + 1)                                 
SET @UpperBound =  @maximumRows * @startRowIndex            
           
DECLARE @SQLQryStr VARCHAR(8000)           
DECLARE @SQLCommand VARCHAR(8000)     
DECLARE @SQLQry VARCHAR(8000)    
DECLARE @SQLQryNew VARCHAR(8000)     
 
Set @SQLQryStr = '            
IF EXISTS (SELECT table_name                    
                 FROM   information_schema.tables                    
                 WHERE  table_name = ''tempExactMatch'')                    
        DROP TABLE tempExactMatch   
SELECT ROW_NUMBER() over( Order by c.Full_Name ) AS RowNumber,                   
   C.BGCM_ID                    
  ,ISNULL(C.STAR_ID,0) AS [OrganizationID]                  
  ,ISNULL(FT.FacilityType_ID ,0) AS FacilityType_ID                
  ,FT.FacilityType_Name                  
  ,c.Full_Name                     
  ,A.Address_Line_1                                                 
  ,A.Address_Line_2                                                 
  ,A.City                 
  ,isnull(sm.State_Id ,0) AS [Address_State_ID]                  
  ,SM.State_Name                   
  ,sm.State_initials +''- ''+ sm.State_Name as State_Province                                     
  ,A.Postal_Area                 
  ,isnull(cnt.Country_Id ,0) as Country_Id                                  
  ,cnt.Country_Name,A.State          
  INTO tempExactMatch                         
 FROM PMP_BGCMCustomer c                                                 
 inner  JOIN PMP_BGCMAddress A ON c.BGCM_ID = A.BGCM_ID                                     
 LEFT OUTER JOIN PMP_Country cnt on a.Country=cnt.Country_Name                    
 LEFT OUTER JOIN PMP_StateMaster sm ON A.State_Province = SM.State_initials                
 LEFT OUTER JOIN PMP_FacilityType FT ON c.Customer_Type = FT.FacilityType_Name                               
 WHERE  1=1  --and cust_type=''Org''  
 and c.First_name is null and rtrim(ltrim(c.full_Name))!='''' and c.full_Name is not null  '                                               
                
SET @SQLQryStr = @SQLQryStr + @whereClause + @ExactSearchClause  
EXEC(@SQLQryStr) 
  
Declare @Cnt int 
Select @Cnt=ISNULL(Max(RowNumber),0) From tempExactMatch 
                          
                                 
SET @SQLCommand = '            
IF EXISTS (SELECT table_name                    
                 FROM   information_schema.tables                    
                 WHERE  table_name = ''tempBestMatch'')                    
        DROP TABLE tempBestMatch   
SELECT ' + Convert(Varchar(10),@Cnt) + '+ROW_NUMBER() over( Order by c.Full_Name ) AS RowNumber,                   
   C.BGCM_ID                    
  ,ISNULL(C.STAR_ID,0) AS [OrganizationID]                  
  ,ISNULL(FT.FacilityType_ID ,0) AS FacilityType_ID                
  ,FT.FacilityType_Name                  
  ,c.Full_Name                     
  ,A.Address_Line_1                                                 
  ,A.Address_Line_2                                                 
  ,A.City                 
  ,isnull(sm.State_Id ,0) AS [Address_State_ID]                  
  ,SM.State_Name                   
  ,sm.State_initials +''- ''+ sm.State_Name as State_Province                                     
  ,A.Postal_Area                 
  ,isnull(cnt.Country_Id ,0) as Country_Id                                  
  ,cnt.Country_Name,A.State          
  INTO tempBestMatch                         
 FROM PMP_BGCMCustomer c                                                 
 inner  JOIN PMP_BGCMAddress A ON c.BGCM_ID = A.BGCM_ID                                     
 LEFT OUTER JOIN PMP_Country cnt on a.Country=cnt.Country_Name                    
 LEFT OUTER JOIN PMP_StateMaster sm ON A.State_Province = SM.State_initials                
 LEFT OUTER JOIN PMP_FacilityType FT ON c.Customer_Type = FT.FacilityType_Name                               
 WHERE  1=1  --and cust_type=''Org''  
 and c.First_name is null and rtrim(ltrim(c.full_Name))!='''' and c.full_Name is not null  '                                               
                
SET @SQLCommand = @SQLCommand + @whereClause + @BestSearchClause  
EXEC(@SQLCommand) 
  
Declare @Count int 
Select @Count=ISNULL(Max(RowNumber),0) From tempBestMatch 
 
SET @SQLQry = '     
IF EXISTS (SELECT table_name                    
         FROM   information_schema.tables                    
         WHERE  table_name = ''tempPartialMatch'')                    
  DROP TABLE tempPartialMatch            
SELECT ' + Convert(Varchar(10),@Count) + '+ROW_NUMBER() over( Order by c.Full_Name ) AS RowNumber,                   
   C.BGCM_ID                    
  ,ISNULL(C.STAR_ID,0) AS [OrganizationID]                  
  ,ISNULL(FT.FacilityType_ID ,0) AS FacilityType_ID                
  ,FT.FacilityType_Name                  
  ,c.Full_Name                     
  ,A.Address_Line_1                                                 
  ,A.Address_Line_2                                                 
  ,A.City                 
  ,isnull(sm.State_Id ,0) AS [Address_State_ID]                  
  ,SM.State_Name                   
  ,sm.State_initials +''- ''+ sm.State_Name as State_Province                                     
  ,A.Postal_Area                 
  ,isnull(cnt.Country_Id ,0) as Country_Id                                  
  ,cnt.Country_Name,A.State          
  INTO tempPartialMatch                         
 FROM PMP_BGCMCustomer c                                                 
 inner  JOIN PMP_BGCMAddress A ON c.BGCM_ID = A.BGCM_ID                                     
 LEFT OUTER JOIN PMP_Country cnt on a.Country=cnt.Country_Name                    
 LEFT OUTER JOIN PMP_StateMaster sm ON A.State_Province = SM.State_initials                
 LEFT OUTER JOIN PMP_FacilityType FT ON c.Customer_Type = FT.FacilityType_Name                               
 WHERE  1=1  --and cust_type=''Org''  
 and c.First_name is null and rtrim(ltrim(c.full_Name))!='''' and c.full_Name is not null  '                                               
                
SET @SQLQry = @SQLQry + @whereClause + @PartialSearchClause  
EXEC(@SQLQry) 
 
 
SET @SQLQryNew = '              
IF EXISTS (SELECT table_name                      
                 FROM   information_schema.tables                      
                 WHERE  table_name = ''Search_Organization'')                      
        DROP TABLE Search_Organization 
SELECT *  
INTO Search_Organization  
FROM ( 
 SELECT * FROM tempExactMatch  
 UNION 
 SELECT * FROM tempBestMatch  
 UNION 
 SELECT * FROM tempPartialMatch )t ' 
 
SET @SQLQryNew = @SQLQryNew +'  order by RowNumber ASC '                                  
-- Table[0]-- Get the records --------------------------------------             
                                 
set @SQLQryNew=@SQLQryNew + ' SELECT * FROM Search_Organization                                                    
 WHERE RowNumber BETWEEN  ' + CAST(@LowerBound AS VARCHAR(10)) + ' AND ' + CAST(@UpperBound  AS VARCHAR(10))                               
           
-- Table[1]-- Get the total rows --------------------------------------  
set @SQLQryNew=@SQLQryNew + ' Select Count(*) FROM Search_Organization '            
           
PRINT @SQLQryNew             
EXEC(@SQLQryNew)             
                             
END