CREATE PROCEDURE [dbo].[sp_GetEntities]
    @TableName NVARCHAR(128),
    @ReturnFields NVARCHAR(2000),
    @TopCount int,
    @SqlFullPopulate NVARCHAR(4000),
    @SqlPopulate NVARCHAR(4000),
    @VariableDecalrations NVARCHAR(1024),
    @ParameterString1 NVARCHAR(512),
    @ParameterString2 NVARCHAR(512),
    @ParameterString3 NVARCHAR(512),
    @ParameterString4 NVARCHAR(512),
    @DisOrderSql NVARCHAR(256),
    @TotalRecords int OUTPUT
AS

DECLARE @totalSQL NVARCHAR(4000)
DECLARE @sqlBegin NVARCHAR(1024)
DECLARE @sqlBegin2 NVARCHAR(1024)
DECLARE @sqlPart1 NVARCHAR(4000)
DECLARE @sqlPart2 NVARCHAR(4000)
DECLARE @sqlPart3 NVARCHAR(4000)
DECLARE @sqlEnd NVARCHAR(1024)

-- Set the begin SQL
set @sqlBegin = N'DECLARE @TotalRecords int;set @TotalRecords = 0;'
set @sqlBegin2 = N'SET NOCOUNT ON;CREATE TABLE #t(IndexId INT IDENTITY (1, 1) NOT NULL,EntityId INT);CREATE TABLE #EntityIdTable(EntityId INT);'

-- Set the end SQL
set @sqlEnd = N'DROP TABLE #t;DROP TABLE #EntityIdTable;SET NOCOUNT OFF'

-- Set the SQL to get the total records count.
set @sqlPart1 = N'EXECUTE sp_executesql N''SELECT @TotalRecords = COUNT(*) FROM (' + @SqlFullPopulate + N') a''' + ', N''' + @ParameterString1 + N'@TotalRecords INT OUTPUT'', ' + @ParameterString2 + N'@TotalRecords OUTPUT'

-- Set the SQL to get the record keys of the current page.
set @sqlPart2 = N'EXECUTE sp_executesql N''' + N'INSERT INTO #t(EntityId) SELECT TOP ' + CAST(@TopCount AS NVARCHAR(20)) + N' EntityId FROM (' + @SqlPopulate + N') t ORDER BY ' + @DisOrderSql + N'''' + @ParameterString3 + @ParameterString4 + N'; INSERT INTO #EntityIdTable SELECT EntityId FROM #t ORDER BY IndexId DESC'

-- Set the SQL to get the record entities of the current page.
set @sqlPart3 = N'EXECUTE sp_executesql N''SELECT ' + @ReturnFields + N' FROM #EntityIdTable dt INNER JOIN [' + @TableName + N'] t ON dt.EntityId = t.EntityId'''

-- Connect the SQL to get the total count
set @totalSQL = @sqlBegin + @VariableDecalrations
+ N'
'
+ @sqlPart1
+ N';set @totalCount = @TotalRecords'

-- Execute the SQL
EXECUTE sp_executesql @totalSQL, N'@totalCount INT OUTPUT', @TotalRecords OUTPUT

-- Connect the SQL to get the current page records
set @totalSQL = @sqlBegin2 + @VariableDecalrations
+ N'
'
+ @sqlPart2
+ N'
'
+ @sqlPart3
+ N'
'
+ @sqlEnd

-- Execute the SQL
EXECUTE sp_executesql @totalSQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_SampleTable](
    [EntityId] [int] IDENTITY(1,1) NOT NULL,
    [c1] [int] NOT NULL,
    [c2] [int] NULL,
    [c3] [varchar](50) NULL,
 CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED 
(
    [EntityId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

CREATE PROCEDURE [dbo].[sp_GetPageDataFromSampleTable]
    @c1 int,
    @c2 int,
    @PageIndex int,
    @PageSize int,
    @TotalRecords int output
AS

DECLARE @tableName NVARCHAR(128)
DECLARE @topCount int
DECLARE @returnFields NVARCHAR(2048)
DECLARE @selectTopFields NVARCHAR(256)
DECLARE @orderFields NVARCHAR(256)
DECLARE @disOrderFields NVARCHAR(256)
declare @variableDeclarations NVARCHAR(2000)
DECLARE @dynamicConditions NVARCHAR(2048)
DECLARE @parameterString1 NVARCHAR(2000)
DECLARE @parameterString2 NVARCHAR(2000)
DECLARE @parameterString3 NVARCHAR(2000)
DECLARE @parameterString4 NVARCHAR(2000)
DECLARE @selectCountSql NVARCHAR(1024) 
DECLARE @selectSql NVARCHAR(1024)

-- begin to set the variable values according to the business logic.

-- set @tableName.
set @tableName = N'tb_SampleTable'

-- set @topCount.
set @topCount = (@pageIndex + 1) * @pageSize

-- set @returnFields.
set @returnFields = N't.EntityId,t.c1,t.c2,t.c3'

-- set @selectTopFields. 
-- Notes: The select top fields must include the entityId and the order fields.
set @selectTopFields = N't.EntityId,t.c3'

-- set @orderFields and @disOrderFields.
set @orderFields = N't.c3 asc'
set @disOrderFields = N't.c3 desc'

-- init the local variables.
set @dynamicConditions = N''
set @variableDeclarations = N''
set @parameterString1 = N''
set @parameterString2 = N''
set @parameterString3 = N''
set @parameterString4 = N''

-- @c1
if not @c1 is null and @c1 > 0
begin
    set @variableDeclarations = @variableDeclarations + N'DECLARE @c1 int; set @c1 = ' + CAST(@c1 AS NVARCHAR(32)) + N';'
    set @dynamicConditions = @dynamicConditions + N' AND t.c1 >= @c1'
    set @parameterString1 = @parameterString1 + N'@c1 int, '
    set @parameterString2 = @parameterString2 + N'@c1, '
    set @parameterString3 = @parameterString3 + N',@c1 int'
    set @parameterString4 = @parameterString4 + N',@c1'
end

-- @c2
if not @c2 is null and @c2 > 0
begin
    set @variableDeclarations = @variableDeclarations + N'DECLARE @c2 int; set @c2 = ' + CAST(@c2 AS NVARCHAR(32)) + N';'
    set @dynamicConditions = @dynamicConditions + N' AND t.c2 >= @c2'
    set @parameterString1 = @parameterString1 + N'@c2 int, '
    set @parameterString2 = @parameterString2 + N'@c2, '
    set @parameterString3 = @parameterString3 + N',@c2 int'
    set @parameterString4 = @parameterString4 + N',@c2'
end

-- set @selectSql and @selectCountSql.
set @selectSql = N'SELECT TOP ' + CAST(@topCount AS NVARCHAR(32)) + N' ' + @selectTopFields + N' FROM tb_SampleTable t WHERE 1 = 1' + @dynamicConditions + ' ORDER BY ' + @orderFields
set @selectCountSql = N'SELECT t.EntityId FROM tb_SampleTable t WHERE 1 = 1' + @dynamicConditions

-- Format @parameterString3 and @parameterString4
if @parameterString3 <> N''
    set @parameterString3 = N', N''' + substring(@parameterString3, 2, len(@parameterString3) - 1) + N''', '
if @parameterString4 <> N''
    set @parameterString4 = substring(@parameterString4, 2, len(@parameterString4) - 1)

-- Call sp_GetEntities to get the records of the current page and the total records count.
exec sp_GetEntities
@tableName,
@returnFields,
@PageSize,
@selectCountSql,
@selectSql,
@variableDeclarations,
@parameterString1,
@parameterString2,
@parameterString3,
@parameterString4,
@disOrderFields,
@TotalRecords output