CREATE PROCEDURE [dbo].[fs_1_usp_query]
@paramerter_client_id   int = null,   
 @paramerter_event_type_id  int = null,   
 @paramerter_start_date         datetime = null,   
 @paramerter_end_date           datetime = null,   
 @paramerter_page_index   int = 1,
paramerter_sort_direction varchar(20),   
 @paramerter_page_count   int = 30   
AS   
BEGIN       
 SET ARITHABORT ON;   
 SET NOCOUNT ON;   
    
 declare @sql nvarchar(max)   
        
 set @sql =      '
    
    create table #output2    
    (   
            page_index                                      int,       
                rownumber                                         int, 
            page_count                          int,    
            client_id                           int,    
            date                                            datetime,   
      )   
    
          
    
          
        --insert into #output1   
  select   
    page_count = count(event_id) over(),       
    table1.*
    into #output1'   
  set @sql = @sql + '   
   from   
    table1 table1 with (nolock)   
   inner join   
    table2 table2 with (nolock)   
   on   
    ............................
   inner join   
    table3 table3 with (nolock)   
   on   
    ............................
   inner join   
    table4 table4
   on   
    ............................
   where   
    ............................
   
 if (@paramerter_client_id is not null)   
  set @sql = @sql + ' and table2.client_id = @paramerter_client_id'   
     
 if (@paramerter_event_type_id is not null)   
  set @sql = @sql + ' and table2.event_type_id = @paramerter_event_type_id'   
     
 if (@paramerter_start_date is not null)   
  set @sql = @sql + ' and table2.created_date >= @paramerter_start_date'   
 if (@paramerter_end_date is not null)   
        set @sql = @sql + ' and table2.created_date <= @paramerter_end_date'   
 
 declare @lv_begin_index int   
 declare @lv_end_index int   
 set @lv_begin_index = ((@paramerter_page_index - 1) * @paramerter_page_count) + 1     
 set @lv_end_index = @lv_begin_index + @paramerter_page_count    
     
 set @sql = @sql +   ' 
     
 UPDATE #output1
    SET osat_rating = ''-''   
    WHERE LEFT( osat_rating , 1 ) = ''-''         
    
 insert into #output2 
 select    
  page_index = ' + convert(varchar, @paramerter_page_index) + ',       
     row_number() over (order by [' + @paramerter_sort_expression + '] '+ @paramerter_sort_direction + ') as rownumber, 
     #output1.* 
 from #output1
   
 select #output2.* 
 from #output2 
 where   
  rownumber >= ' + convert(varchar, @lv_begin_index) + '   
 and   
  rownumber < ' + convert(varchar, @lv_end_index)    '   
    
 set @sql = @sql + '    
 drop table #output1    
 drop table #output2  '