Row number with join group by paging temporary table with output parameter SQL

 USE [PMS]

GO

/****** Object:  StoredProcedure [dbo].[read_cadre_info_paging]    Script Date: 07-12-2022 10:06:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[read_cadre_info_paging]

@postheld varchar(50),

@StartIndex int,

@PageSize int,

@TotalCount int OutPut

AS

BEGIN

with cte as (select  Max(ee.ID) as ID,MainID,'temp'=Count(MainID),disc.Discipline,disc.DisciplineID,'AdjustedDiscipline'=Adisc.Discipline,'AdjustedDisciplineID'=ee.AdjustedDiscipline,Title,Name,'FMSID'=RIGHT('00000'+convert(varchar(6),ee.FMSID),6),Gender,Category,RosterCategory,DOB,AppointmentDate,AppointmentDateSS,'PresentDesignation'=ids.Designation,AppointmentDatePresent,PresentInstitutePostingDate,'Institute'=inst.I_Aym+', '+inst.I_City,'InstituteID'=inst.InstituteID_cms,DOR,'SMD'=smd.Div_Name,'SMDID'=smd.Div_Code,Mobile,ee.Email,Remarks,InstituteRemarks,'State'=st.State_Name,'StateID'=st.State_Id,DeputationFrom,DeputationTo,StudyLeaveFrom,StudyLeaveTo,WSP,'RegionalStation'=ip.I_Aym+','+ip.I_City,ee.DateTime,'PostHeld'=ict.CadreType,'CadreTypeID'=ee.PostHeld,'PresentDesignationID'=ids.DesignationID,'AiprTime'=af.DateTime,'AiprFile'=af.AIPRFile,'VigilanceStatus'=ivt.StatusText,'VigilanceStatusId'=ivt.Id,ee.PreviousOrganization,au.emp_code,do.State_Name as domicile_name,do.State_Id as domicile_id from icms_employee ee

left join icms_Discipline disc on disc.DisciplineID=ee.Discipline

left join icms_Discipline Adisc on Adisc.DisciplineID=ee.AdjustedDiscipline

left join Permisnet_new.dbo.Institute inst on inst.InstituteID_cms=ee.Institute

left join Permisnet_new.dbo.Division smd on smd.Div_Code=inst.Div_Code 

left join Permisnet_new.dbo.State st on st.State_Id=inst.I_State_ID

left join Permisnet_new.dbo.Institute ip on ip.InstituteID_cms=ee.RegionalStation

left join icms_CadreType ict on ict.CadreTypeID=ee.PostHeld

left join icms_Designation ids on ids.DesignationID=ee.PresentDesignation

left join (select * from AIPR_Files where Flag='1' and aipr_year=YEAR(GETDATE())) af on af.FMSID=ee.FMSID--year wise AIPR

left join (SELECT  State_Id,State_Name,profile_pic,m_tongue,erp_id

  FROM Add_info ai join Permisnet_new.dbo.State s on s.State_Id=ai.domicile)  do on do.erp_id=ee.FMSID

left join (select * from icms_VigilanceStatus where Flag='1') iv on iv.FMSID=ee.FMSID

left join (select * from pms.dbo.Auth_user) au on au.erp_id=ee.FMSID

left join icms_VigilanceStatusTypes ivt on ivt.Id=iv.VigilanceStatus

where PostHeld=@postheld and ee.Flag='true' and Retired='false' 

group by MainID,ee.ID,disc.Discipline,disc.DisciplineID,Adisc.Discipline,ee.AdjustedDiscipline,Title,Name,ee.FMSID,Gender,Category,RosterCategory,DOB,AppointmentDate,AppointmentDateSS,ids.Designation,AppointmentDatePresent,PresentInstitutePostingDate,inst.I_Aym,inst.I_City,inst.InstituteID_cms,DOR,smd.Div_Name,smd.Div_Code,Mobile,ee.Email,Remarks,InstituteRemarks,st.State_Name,st.State_Id,DeputationFrom,DeputationTo,StudyLeaveFrom,StudyLeaveTo,WSP,ip.I_Name,ip.i_aym,ip.I_City,ee.DateTime,ict.CadreType,ee.PostHeld,ids.DesignationID,af.DateTime,af.AIPRFile,ivt.StatusText,ivt.Id,ee.PreviousOrganization,au.emp_code,do.State_Name,do.State_Id ),

cte1 as(

select ROW_NUMBER() over(order by Institute, Discipline, PresentInstitutePostingDate asc) as RowNumber,* from cte 

)

select * into #t from cte1

SET @TotalCount = (SELECT count(*) from #t)

SELECT  * from #t where RowNumber between @startIndex and (@startIndex+@PageSize-1)

END