Classic paging Stored Procedure

Code
Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
Go

Alter procedure [dbo]. [sp_GetEntities]
@ PageSize int,
@ SQL fullpopulate varchar (4000 ),
@ SqlPopulate varchar (4000 ),
@ OrderSql varchar (400 ),
@ GetRelatedEntities bit,
@ GetChildEntities bit,
@ RelatedTypes varchar (200 ),
@ ReturnFields varchar (4000 ),
@ RelatedEntityReturnFields varchar (4000 ),
@ ChildEntityReturnFields varchar (4000 ),
@ TotalRecords int output
AS

SET NOCOUNT ON

Create table # t
(
IndexID int IDENTITY (1, 1) not null,
Entityid int
)
Create Table # entityidtable
(
Entityid int
)
Create Table # relatedpairentityidtabletemp
(
Indexid int identity (1, 1) not null,
Entityid int,
Relatedentityid int,
Relationtype int
)
Create Table # relatedentityidtabletemp
(
Indexid int identity (1, 1) not null,
Entityid int
)
Create Table # relatedentityidtable
(
EntityID int
)
-- Get total records count
Declare @ SQL nvarchar (4000)
Set @ SQL = n' select @ TotalRecords = count (*) from '+ N' (' + @ sqlFullPopulate + N')'
EXEC sp_executesql @ SQL, n' @ TotalRecords int output', @ TotalRecords output

-- Get the records which in the current page
Set @ SQL = 'insert into # t (entityid) select top' + cast (@ pageSize as varchar (20) + 'entityid from ('+ @ sqlPopulate + ') a' + @ orderSql
EXEC (@ SQL)
Insert into # EntityIDTable select entityid from # t order by IndexID desc

-- Get the main entities
Set @ SQL = 'select' + @ ReturnFields + 'from # EntityIDTable eid inner join tb_entities E on eid. entityid = e. entityid'
EXEC (@ SQL)

-- Get the related entities
If @ GetRelatedEntities = 1 and (not @ RelatedTypes is null) and @ RelatedTypes <>''
Begin
Set @ SQL = 'insert into # RelatedPairEntityIDTableTemp (entityid, relatedentityid, relationtype) select er. entityid, er. relatedentityid, er. relationtype from tb_entityrelations er where er. relationtype in ('+ @ RelatedTypes +') and (er. entityid in (select eid. entityid from # entityidtable eid) or er. relatedentityid in (select eid. entityid from # entityidtable eid ))'
Exec (@ SQL)
Insert into # RelatedEntityIDTableTemp (entityid) select entityid from # RelatedPairEntityIDTableTemp where entityid not in (select eid. entityid from # entityidtable eid)
Insert into # RelatedEntityIDTableTemp (entityid) select relatedentityid from # RelatedPairEntityIDTableTemp where relatedentityid not in (select eid. entityid from # entityidtable eid)
Insert into # RelatedEntityIDTable (entityid) select distinct entityid from # RelatedEntityIDTableTemp
Set @ SQL = 'select' + @ RelatedEntityReturnFields + 'from # RelatedEntityIDTable reid join tb_entities e on reid. entityid = e. entityid'
Exec (@ SQL)
Select entityid, RelatedEntityID, relationtype from # RelatedPairEntityIDTableTemp
End

-- Get the child entities
If @ getchildentities = 1
Begin
Set @ SQL = 'select' + @ childentityreturnfields + 'from tb_entities e where E. parentid in (select entityid as parentid from # entityidtable )'
Exec (@ SQL)
End

Drop table # T
Drop table # entityidtable
Drop table # relatedpairentityidtabletemp
Drop table # relatedentityidtabletemp
Drop table # relatedentityidtable

Set nocount off