Ranish666

for pagination

Mar 21st, 2021
818
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ALTER proc [dbo].[Register_getTotal]  
  2. @type int,      
  3. @fromdate datetime,      
  4. @todate datetime,      
  5. @groupid int,      
  6. @ledgerid int,      
  7. @yearid int ,
  8. @invoiceNo nvarchar(max),
  9. @narration nvarchar(max),    
  10. @pageNo int,    
  11. @pagerow int  
  12. as  
  13.  
  14. DECLARE @firstRecord int = (@pageNo - 1) * @pagerow + 1;  
  15. DECLARE @lastRecord int = @firstRecord + @pagerow - 1;  
  16. --Declare @Pages int =  
  17.  
  18. with totalTem as(  
  19. select distinct MasterId,      
  20. te.date, te.EntryDate,    
  21. tlp.voucherTypeId, tlp.voucherNo, tlp.invoiceNo, totalAmount, te.narration, te.isImport,      
  22. te.isfree, te.isvatPurchase, te.isfixedassests, te.userId, financialYearId, te.BranchID      
  23. from tbl_EntryMaster te      
  24. inner join tbl_ledgerposting tlp on tlp.entryMasterID = te.MasterId      
  25. inner join tbl_AccountLedger tal on tal.ledgerId = tlp.ledgerId      
  26. where
  27.    tlp.voucherTypeId = @type      
  28.    and tlp.ledgerId = case when @ledgerid = 0 then tal.ledgerId else @ledgerid end      
  29.    and tal.accountGroupId = case when @groupid = 0 then tal.accountGroupId else @groupid end      
  30.    and convert(date, te.date) between convert(date, @fromdate) and convert(date, @todate)      
  31.    and te.financialYearId = @yearid      
  32.    and te.invoiceNo like @invoiceNo+'%'
  33.    and isnull(te.narration,'') like '%'+@narration+'%'  
  34. group by MasterId, te.date, te.EntryDate, tlp.voucherTypeId, tlp.voucherNo, tlp.invoiceNo, totalAmount, te.narration, te.isImport,      
  35. te.isfree, te.isvatPurchase, te.isfixedassests, te.userId, financialYearId, te.BranchID    
  36. )  
  37. select @pagerow as pageRows , count (*) as totalRows, @firstRecord as firstRecord,  
  38. case when count(*) > @pagerow then @lastRecord  
  39. else @firstRecord + count(*) - 1 end  
  40. as lastRecord, Ceiling(CONVERT(decimal,count(*))/@pagerow)  
  41. as TotalPages, @pageNo as Currentpage from totalTem  
RAW Paste Data