wcf - Paging using WPF and using stored procedure -

paging control used in wpf application. paging controls created in stored procedure after wcf application refer stored procedure.

i have using paging control in stored procedure.

set ansi_nulls on go set quoted_identifier on go  create procedure [dbo].[usp_employee] (     /* properties*/     @employeeid int=null,     @employeename nvarchar(50)=null,     @employeeaddress nvarchar(50)=null,     @employeephoneno nvarchar(50)=null,     @action varchar(10)='action',      /*paging parameter */     @pagenumber int=1,     @pagesize int=5,      /*sorting parameter */     @sortcolumn nvarchar(20)='title',     @sortorder nvarchar(10)=''  )  begin     /* declaring local variables corresponding parameter modifications*/     declare     @lemployeeid int,     @lemployeename nvarchar(50),     @lemployeeaddress nvarchar(50),     @lemployeephoneno nvarchar(50),      @lpagenumber int,     @lpagesize int,     @lsortcolumn nvarchar(20),     @lfirstrecord int,     @llastrecord int,     @ltotalrows int  set nocount on;    if @action = 'insert'     begin          insert utb_employee(employeename,employeeaddress,employeephoneno)          values (@employeename,@employeeaddress,@employeephoneno)          end     if @action = 'update'                                                                                                                                                                                                                                                                                                                                                                                    begin           update utb_employee          set employeename=@employeename,employeeaddress=@employeeaddress,employeephoneno=@employeephoneno           employeeid=@employeeid      end               if @action = 'select'      begin            select * utb_employee            employeeid = @employeeid      end      if @action='get'     begin     /*setting local variables*/     set @lemployeeid=@employeeid     set @lemployeename=@employeename     set @lemployeeaddress=@employeeaddress     set @lemployeephoneno=@employeephoneno      set @lpagenumber = @pagenumber     set @lpagesize = @pagesize     set @lsortcolumn = ltrim(rtrim(@sortcolumn))      set @lfirstrecord = ( @lpagenumber - 1 ) * @lpagesize     set @llastrecord = ( @lpagenumber * @lpagesize + 1 )     set @ltotalrows = @lfirstrecord - @llastrecord + 1;       cte_results     (         select row_number() on (order         case when @lsortcolumn='employeeid' , @sortorder='asc'             employeeid         end asc,         case when @lsortcolumn='employeeid' , @sortorder='desc'             employeeid         end desc,          case when @lsortcolumn='employeename' , @sortorder='asc'             employeename         end asc,         case when @lsortcolumn='employeename' , @sortorder='desc'             employeename         end desc,          case when @lsortcolumn='employeeaddress' , @sortorder='asc'             employeeaddress         end asc,         case when @lsortcolumn='employeeaddress' , @sortorder='desc'             employeeaddress         end desc,          case when @lsortcolumn='employeephoneno' , @sortorder='asc'             employeephoneno         end asc,         case when @lsortcolumn='employeephoneno' , @sortorder='desc'             employeephoneno         end desc          ) rownum,     count(*) over() totalcount,     employeeid,     employeename,     employeeaddress,     employeephoneno utb_employee      )     select employeeid,employeename,employeeaddress,employeephoneno,totalcount cte_results cpc      rownum > @lfirstrecord             , rownum < @llastrecord     order rownum asc     end   end