Help with Paging SPROC

I'm trying to learn from SQLMag.com's InstantDoc #40505 called "Server-Side
Paging with SQL Server "
This was a well written main feature story, however, their syntax in Listing
4 below is giving me errors. Can someone paste the below code into QA and
help me find which quotes are incorrect? I've never dealt with "Search
Variable" syntax before and want to learn from this article.
CODE from
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=40505&DisplayTab=Article[
/url]
=============
/* Listing 4: SELECT_WITH_PAGING Stored Procedure */
CREATE PROCEDURE SELECT_WITH_PAGING (
@.strFields varchar(4000),
@.strPK varchar(100),
@.strTables varchar(4000),
@.intPageNo int = 1,
@.intPageSize int = NULL,
@.blnGetRecordCount bit = 0,
@.strFilter varchar(8000) = NULL,
@.strSort varchar(8000) = NULL,
@.strGroup varchar(8000) = NULL)
/* Executes a SELECT statement that the parameters define,and returns a
particular page of data (or all rows) efficiently. */
AS
DECLARE @.blnBringAllRecords bit
DECLARE @.strPageNo varchar(50)
DECLARE @.strPageSize varchar(50)
DECLARE @.strSkippedRows varchar(50)
DECLARE @.strFilterCriteria varchar(8000)
DECLARE @.strSimpleFilter varchar(8000)
DECLARE @.strSortCriteria varchar(8000)
DECLARE @.strGroupCriteria varchar(8000)
DECLARE @.intRecordcount int
DECLARE @.intPagecount int
/* Normalize the paging criteria.
If no meaningful inputs are provided, we can avoid paging and execute a more
efficient query, so we will
set a flag that will help avoid paging (blnBringAllRecords). */
IF @.intPageNo < 1
SET @.intPageNo = 1
SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
IF @.intPageSize IS NULL OR @.intPageSize < 1 -- Bring all records,
don't do paging.
SET @.blnBringAllRecords = 1
ELSE
BEGIN
SET @.blnBringAllRecords = 0
SET @.strPageSize = CONVERT(varchar(50), @.intPageSize)
SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
SET @.strSkippedRows = CONVERT(varchar(50), @.intPageSize *
(@.intPageNo - 1))
END
/* Normalize the filter and sorting criteria.
If the criteria are empty, we will avoid filtering and sorting,
respectively, by executing more efficient
queries. */
IF @.strFilter IS NOT NULL AND @.strFilter != ''
BEGIN
SET @.strFilterCriteria = ' WHERE ' + @.strFilter + ' '
SET @.strSimpleFilter = ' AND ' + @.strFilter + ' '
END
ELSE
BEGIN
SET @.strSimpleFilter = ''
SET @.strFilterCriteria = ''
END
IF @.strSort IS NOT NULL AND @.strSort != ''
SET @.strSortCriteria = ' ORDER BY ' + @.strSort + ' '
ELSE
SET @.strSortCriteria = ''
IF @.strGroup IS NOT NULL AND @.strGroup != ''
SET @.strGroupCriteria = 'GROUP BY' + @.strGroup + ' '
ELSE
SET @.strGroupCriteria = ''
/* Now start doing the real work. */
IF @.blnBringAllRecords = 1 -- Ignore paging and run a
simple SELECT.
BEGIN
EXEC (
'SELECT ' + @.strFields + 'FROM' + @.strTables + @.strFilterCriteria +
@.strGroupCriteria + @.strSortCriteria
)
END -- We had to bring all
records.
ELSE -- Bring only a
particular page.
BEGIN
IF @.intPageNo = 1 -- In this case we can
execute a more efficient
-- query
with no subqueries.
EXEC (
'SELECT TOP' + @.strPageSize + ' ' + @.strFields + 'FROM' +
@.strTables +
@.strFilterCriteria + @.strGroupCriteria + @.strSortCriteria
)
ELSE -- Execute a structure of
subqueries that brings the correct page.
EXEC (
'SELECT' + @.strFields + 'FROM' + @.strTables + 'WHERE' + @.strPK +
'IN' + '
(SELECT TOP' + @.strPageSize + ' ' + @.strPK + 'FROM' + @.strTables
+
' WHERE' + @.strPK + 'NOT IN' + '
(SELECT TOP' + @.strSkippedRows + ' ' + @.strPK + 'FROM' +
@.strTables +
@.strFilterCriteria + @.strGroupCriteria +
@.strSortCriteria + ') ' +
@.strSimpleFilter +
@.strGroupCriteria +
@.strSortCriteria + ') ' +
@.strGroupCriteria +
@.strSortCriteria
)
END -- We had to bring a
particular page.
/* If we need to return the recordcount: */
IF @.blnGetRecordCount = 1
IF @.strGroupCriteria != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
@.strTables + @.strFilterCriteria + @.strGroupCriteria + ') AS tbl
(id)
)
ELSE
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM' + @.strTables +
@.strFilterCriteria +
@.strGroupCriteria
)
GOI think most of the problems are with comments wrapping and breaking down
into 2 lines. There's a missing quote below as well. I correct it and see if
it works for you. Hope the lines won't wrap this time.
/* Listing 4: SELECT_WITH_PAGING Stored Procedure */
CREATE PROCEDURE SELECT_WITH_PAGING (
@.strFields varchar(4000),
@.strPK varchar(100),
@.strTables varchar(4000),
@.intPageNo int = 1,
@.intPageSize int = NULL,
@.blnGetRecordCount bit = 0,
@.strFilter varchar(8000) = NULL,
@.strSort varchar(8000) = NULL,
@.strGroup varchar(8000) = NULL)
/* Executes a SELECT statement that the parameters define,and returns a
particular page of data (or all rows) efficiently. */
AS
DECLARE @.blnBringAllRecords bit
DECLARE @.strPageNo varchar(50)
DECLARE @.strPageSize varchar(50)
DECLARE @.strSkippedRows varchar(50)
DECLARE @.strFilterCriteria varchar(8000)
DECLARE @.strSimpleFilter varchar(8000)
DECLARE @.strSortCriteria varchar(8000)
DECLARE @.strGroupCriteria varchar(8000)
DECLARE @.intRecordcount int
DECLARE @.intPagecount int
/* Normalize the paging criteria.
If no meaningful inputs are provided, we can avoid paging and execute a more
efficient query, so we will
set a flag that will help avoid paging (blnBringAllRecords). */
IF @.intPageNo < 1
SET @.intPageNo = 1
SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
IF @.intPageSize IS NULL OR @.intPageSize < 1 -- Bring all records,
don't do paging.
SET @.blnBringAllRecords = 1
ELSE
BEGIN
SET @.blnBringAllRecords = 0
SET @.strPageSize = CONVERT(varchar(50), @.intPageSize)
SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
SET @.strSkippedRows = CONVERT(varchar(50), @.intPageSize *
(@.intPageNo - 1))
END
/* Normalize the filter and sorting criteria.
If the criteria are empty, we will avoid filtering and sorting,
respectively, by executing more efficient
queries. */
IF @.strFilter IS NOT NULL AND @.strFilter != ''
BEGIN
SET @.strFilterCriteria = ' WHERE ' + @.strFilter + ' '
SET @.strSimpleFilter = ' AND ' + @.strFilter + ' '
END
ELSE
BEGIN
SET @.strSimpleFilter = ''
SET @.strFilterCriteria = ''
END
IF @.strSort IS NOT NULL AND @.strSort != ''
SET @.strSortCriteria = ' ORDER BY ' + @.strSort + ' '
ELSE
SET @.strSortCriteria = ''
IF @.strGroup IS NOT NULL AND @.strGroup != ''
SET @.strGroupCriteria = 'GROUP BY' + @.strGroup + ' '
ELSE
SET @.strGroupCriteria = ''
/* Now start doing the real work. */
IF @.blnBringAllRecords = 1 -- Ignore paging and run a
simple SELECT.
BEGIN
EXEC (
'SELECT ' + @.strFields + 'FROM' + @.strTables + @.strFilterCriteria +
@.strGroupCriteria + @.strSortCriteria
)
END -- We had to bring all
records.
ELSE -- Bring only a
particular page.
BEGIN
IF @.intPageNo = 1 -- In this case we can
execute a more efficient
-- query
with no subqueries.
EXEC (
'SELECT TOP' + @.strPageSize + ' ' + @.strFields + 'FROM' +
@.strTables +
@.strFilterCriteria + @.strGroupCriteria + @.strSortCriteria
)
ELSE -- Execute a structure of
subqueries that brings the correct page.
EXEC (
'SELECT' + @.strFields + 'FROM' + @.strTables + 'WHERE' + @.strPK +
'IN' + '
(SELECT TOP' + @.strPageSize + ' ' + @.strPK + 'FROM' + @.strTables
+
' WHERE' + @.strPK + 'NOT IN' + '
(SELECT TOP' + @.strSkippedRows + ' ' + @.strPK + 'FROM' +
@.strTables +
@.strFilterCriteria + @.strGroupCriteria +
@.strSortCriteria + ') ' +
@.strSimpleFilter +
@.strGroupCriteria +
@.strSortCriteria + ') ' +
@.strGroupCriteria +
@.strSortCriteria
)
END -- We had to bring a
particular page.
/* If we need to return the recordcount: */
IF @.blnGetRecordCount = 1
IF @.strGroupCriteria != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
@.strTables + @.strFilterCriteria + @.strGroupCriteria + ') AS tbl
(id)'
)
ELSE
EXEC ( 'SELECT COUNT(*) AS RECORDCOUNT FROM' + @.strTables +
@.strFilterCriteria + @.strGroupCriteria
)
GO
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"scott" <sbailey@.mileslumber.com> wrote in message
news:Okf9SkiXFHA.2684@.TK2MSFTNGP09.phx.gbl...
> I'm trying to learn from SQLMag.com's InstantDoc #40505 called
"Server-Side
> Paging with SQL Server "
> This was a well written main feature story, however, their syntax in
Listing
> 4 below is giving me errors. Can someone paste the below code into QA and
> help me find which quotes are incorrect? I've never dealt with "Search
> Variable" syntax before and want to learn from this article.
>
> CODE from
>
http://www.windowsitpro.com/Article...playTab=Articlekred">
> =============
> /* Listing 4: SELECT_WITH_PAGING Stored Procedure */
> CREATE PROCEDURE SELECT_WITH_PAGING (
> @.strFields varchar(4000),
> @.strPK varchar(100),
> @.strTables varchar(4000),
> @.intPageNo int = 1,
> @.intPageSize int = NULL,
> @.blnGetRecordCount bit = 0,
> @.strFilter varchar(8000) = NULL,
> @.strSort varchar(8000) = NULL,
> @.strGroup varchar(8000) = NULL)
> /* Executes a SELECT statement that the parameters define,and returns a
> particular page of data (or all rows) efficiently. */
> AS
> DECLARE @.blnBringAllRecords bit
> DECLARE @.strPageNo varchar(50)
> DECLARE @.strPageSize varchar(50)
> DECLARE @.strSkippedRows varchar(50)
> DECLARE @.strFilterCriteria varchar(8000)
> DECLARE @.strSimpleFilter varchar(8000)
> DECLARE @.strSortCriteria varchar(8000)
> DECLARE @.strGroupCriteria varchar(8000)
> DECLARE @.intRecordcount int
> DECLARE @.intPagecount int
> /* Normalize the paging criteria.
> If no meaningful inputs are provided, we can avoid paging and execute a
more
> efficient query, so we will
> set a flag that will help avoid paging (blnBringAllRecords). */
> IF @.intPageNo < 1
> SET @.intPageNo = 1
> SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
> IF @.intPageSize IS NULL OR @.intPageSize < 1 -- Bring all
records,
> don't do paging.
> SET @.blnBringAllRecords = 1
> ELSE
> BEGIN
> SET @.blnBringAllRecords = 0
> SET @.strPageSize = CONVERT(varchar(50), @.intPageSize)
> SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
> SET @.strSkippedRows = CONVERT(varchar(50), @.intPageSize *
> (@.intPageNo - 1))
> END
> /* Normalize the filter and sorting criteria.
> If the criteria are empty, we will avoid filtering and sorting,
> respectively, by executing more efficient
> queries. */
> IF @.strFilter IS NOT NULL AND @.strFilter != ''
> BEGIN
> SET @.strFilterCriteria = ' WHERE ' + @.strFilter + ' '
> SET @.strSimpleFilter = ' AND ' + @.strFilter + ' '
> END
> ELSE
> BEGIN
> SET @.strSimpleFilter = ''
> SET @.strFilterCriteria = ''
> END
> IF @.strSort IS NOT NULL AND @.strSort != ''
> SET @.strSortCriteria = ' ORDER BY ' + @.strSort + ' '
> ELSE
> SET @.strSortCriteria = ''
> IF @.strGroup IS NOT NULL AND @.strGroup != ''
> SET @.strGroupCriteria = 'GROUP BY' + @.strGroup + ' '
> ELSE
> SET @.strGroupCriteria = ''
> /* Now start doing the real work. */
> IF @.blnBringAllRecords = 1 -- Ignore paging and run a
> simple SELECT.
> BEGIN
> EXEC (
> 'SELECT ' + @.strFields + 'FROM' + @.strTables + @.strFilterCriteria +
> @.strGroupCriteria + @.strSortCriteria
> )
> END -- We had to bring all
> records.
> ELSE -- Bring only a
> particular page.
> BEGIN
> IF @.intPageNo = 1 -- In this case we can
> execute a more efficient
> -- query
> with no subqueries.
> EXEC (
> 'SELECT TOP' + @.strPageSize + ' ' + @.strFields + 'FROM' +
> @.strTables +
> @.strFilterCriteria + @.strGroupCriteria + @.strSortCriteria
> )
> ELSE -- Execute a structure
of
> subqueries that brings the correct page.
> EXEC (
> 'SELECT' + @.strFields + 'FROM' + @.strTables + 'WHERE' + @.strPK +
> 'IN' + '
> (SELECT TOP' + @.strPageSize + ' ' + @.strPK + 'FROM' +
@.strTables
> +
> ' WHERE' + @.strPK + 'NOT IN' + '
> (SELECT TOP' + @.strSkippedRows + ' ' + @.strPK + 'FROM' +
> @.strTables +
> @.strFilterCriteria + @.strGroupCriteria +
> @.strSortCriteria + ') ' +
> @.strSimpleFilter +
> @.strGroupCriteria +
> @.strSortCriteria + ') ' +
> @.strGroupCriteria +
> @.strSortCriteria
> )
> END -- We had to bring a
> particular page.
> /* If we need to return the recordcount: */
> IF @.blnGetRecordCount = 1
> IF @.strGroupCriteria != ''
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
> @.strTables + @.strFilterCriteria + @.strGroupCriteria + ') AS
tbl
> (id)
> )
> ELSE
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM' + @.strTables +
> @.strFilterCriteria +
> @.strGroupCriteria
> )
> GO
>
>
>|||i finally got it into sql, but it renders errors. i'm giving up and going at
another way. thanks for your help though.
you'd think sqlmag.com wouldn't print errant code.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uM8N2pjXFHA.2996@.TK2MSFTNGP10.phx.gbl...
>I think most of the problems are with comments wrapping and breaking down
> into 2 lines. There's a missing quote below as well. I correct it and see
> if
> it works for you. Hope the lines won't wrap this time.
> /* Listing 4: SELECT_WITH_PAGING Stored Procedure */
> CREATE PROCEDURE SELECT_WITH_PAGING (
> @.strFields varchar(4000),
> @.strPK varchar(100),
> @.strTables varchar(4000),
> @.intPageNo int = 1,
> @.intPageSize int = NULL,
> @.blnGetRecordCount bit = 0,
> @.strFilter varchar(8000) = NULL,
> @.strSort varchar(8000) = NULL,
> @.strGroup varchar(8000) = NULL)
> /* Executes a SELECT statement that the parameters define,and returns a
> particular page of data (or all rows) efficiently. */
> AS
> DECLARE @.blnBringAllRecords bit
> DECLARE @.strPageNo varchar(50)
> DECLARE @.strPageSize varchar(50)
> DECLARE @.strSkippedRows varchar(50)
> DECLARE @.strFilterCriteria varchar(8000)
> DECLARE @.strSimpleFilter varchar(8000)
> DECLARE @.strSortCriteria varchar(8000)
> DECLARE @.strGroupCriteria varchar(8000)
> DECLARE @.intRecordcount int
> DECLARE @.intPagecount int
> /* Normalize the paging criteria.
> If no meaningful inputs are provided, we can avoid paging and execute a
> more
> efficient query, so we will
> set a flag that will help avoid paging (blnBringAllRecords). */
> IF @.intPageNo < 1
> SET @.intPageNo = 1
> SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
> IF @.intPageSize IS NULL OR @.intPageSize < 1 -- Bring all
> records,
> don't do paging.
> SET @.blnBringAllRecords = 1
> ELSE
> BEGIN
> SET @.blnBringAllRecords = 0
> SET @.strPageSize = CONVERT(varchar(50), @.intPageSize)
> SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
> SET @.strSkippedRows = CONVERT(varchar(50), @.intPageSize *
> (@.intPageNo - 1))
> END
> /* Normalize the filter and sorting criteria.
> If the criteria are empty, we will avoid filtering and sorting,
> respectively, by executing more efficient
> queries. */
> IF @.strFilter IS NOT NULL AND @.strFilter != ''
> BEGIN
> SET @.strFilterCriteria = ' WHERE ' + @.strFilter + ' '
> SET @.strSimpleFilter = ' AND ' + @.strFilter + ' '
> END
> ELSE
> BEGIN
> SET @.strSimpleFilter = ''
> SET @.strFilterCriteria = ''
> END
> IF @.strSort IS NOT NULL AND @.strSort != ''
> SET @.strSortCriteria = ' ORDER BY ' + @.strSort + ' '
> ELSE
> SET @.strSortCriteria = ''
> IF @.strGroup IS NOT NULL AND @.strGroup != ''
> SET @.strGroupCriteria = 'GROUP BY' + @.strGroup + ' '
> ELSE
> SET @.strGroupCriteria = ''
> /* Now start doing the real work. */
> IF @.blnBringAllRecords = 1 -- Ignore paging and run a
> simple SELECT.
> BEGIN
> EXEC (
> 'SELECT ' + @.strFields + 'FROM' + @.strTables + @.strFilterCriteria +
> @.strGroupCriteria + @.strSortCriteria
> )
> END -- We had to bring all
> records.
> ELSE -- Bring only a
> particular page.
> BEGIN
> IF @.intPageNo = 1 -- In this case we can
> execute a more efficient
> -- query
> with no subqueries.
> EXEC (
> 'SELECT TOP' + @.strPageSize + ' ' + @.strFields + 'FROM' +
> @.strTables +
> @.strFilterCriteria + @.strGroupCriteria + @.strSortCriteria
> )
> ELSE -- Execute a structure of
> subqueries that brings the correct page.
> EXEC (
> 'SELECT' + @.strFields + 'FROM' + @.strTables + 'WHERE' + @.strPK +
> 'IN' + '
> (SELECT TOP' + @.strPageSize + ' ' + @.strPK + 'FROM' +
> @.strTables
> +
> ' WHERE' + @.strPK + 'NOT IN' + '
> (SELECT TOP' + @.strSkippedRows + ' ' + @.strPK + 'FROM' +
> @.strTables +
> @.strFilterCriteria + @.strGroupCriteria +
> @.strSortCriteria + ') ' +
> @.strSimpleFilter +
> @.strGroupCriteria +
> @.strSortCriteria + ') ' +
> @.strGroupCriteria +
> @.strSortCriteria
> )
> END -- We had to bring a
> particular page.
> /* If we need to return the recordcount: */
> IF @.blnGetRecordCount = 1
> IF @.strGroupCriteria != ''
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
> @.strTables + @.strFilterCriteria + @.strGroupCriteria + ') AS tbl
> (id)'
> )
> ELSE
> EXEC ( 'SELECT COUNT(*) AS RECORDCOUNT FROM' + @.strTables +
> @.strFilterCriteria + @.strGroupCriteria
> )
> GO
>
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:Okf9SkiXFHA.2684@.TK2MSFTNGP09.phx.gbl...
> "Server-Side
> Listing
> http://www.windowsitpro.com/Article...Articl
e
> more
> records,
> of
> @.strTables
> tbl
>|||Check out this article on ASPFAQ: http://www.aspfaq.com/show.asp?id=2120
"scott" <sbailey@.mileslumber.com> wrote in message
news:Okf9SkiXFHA.2684@.TK2MSFTNGP09.phx.gbl...
> I'm trying to learn from SQLMag.com's InstantDoc #40505 called
> "Server-Side Paging with SQL Server "
> This was a well written main feature story, however, their syntax in
> Listing 4 below is giving me errors. Can someone paste the below code into
> QA and help me find which quotes are incorrect? I've never dealt with
> "Search Variable" syntax before and want to learn from this article.
>
> CODE from
> http://www.windowsitpro.com/Article...Articl
e
> =============
> /* Listing 4: SELECT_WITH_PAGING Stored Procedure */
> CREATE PROCEDURE SELECT_WITH_PAGING (
> @.strFields varchar(4000),
> @.strPK varchar(100),
> @.strTables varchar(4000),
> @.intPageNo int = 1,
> @.intPageSize int = NULL,
> @.blnGetRecordCount bit = 0,
> @.strFilter varchar(8000) = NULL,
> @.strSort varchar(8000) = NULL,
> @.strGroup varchar(8000) = NULL)
> /* Executes a SELECT statement that the parameters define,and returns a
> particular page of data (or all rows) efficiently. */
> AS
> DECLARE @.blnBringAllRecords bit
> DECLARE @.strPageNo varchar(50)
> DECLARE @.strPageSize varchar(50)
> DECLARE @.strSkippedRows varchar(50)
> DECLARE @.strFilterCriteria varchar(8000)
> DECLARE @.strSimpleFilter varchar(8000)
> DECLARE @.strSortCriteria varchar(8000)
> DECLARE @.strGroupCriteria varchar(8000)
> DECLARE @.intRecordcount int
> DECLARE @.intPagecount int
> /* Normalize the paging criteria.
> If no meaningful inputs are provided, we can avoid paging and execute a
> more efficient query, so we will
> set a flag that will help avoid paging (blnBringAllRecords). */
> IF @.intPageNo < 1
> SET @.intPageNo = 1
> SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
> IF @.intPageSize IS NULL OR @.intPageSize < 1 -- Bring all
> records, don't do paging.
> SET @.blnBringAllRecords = 1
> ELSE
> BEGIN
> SET @.blnBringAllRecords = 0
> SET @.strPageSize = CONVERT(varchar(50), @.intPageSize)
> SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
> SET @.strSkippedRows = CONVERT(varchar(50), @.intPageSize *
> (@.intPageNo - 1))
> END
> /* Normalize the filter and sorting criteria.
> If the criteria are empty, we will avoid filtering and sorting,
> respectively, by executing more efficient
> queries. */
> IF @.strFilter IS NOT NULL AND @.strFilter != ''
> BEGIN
> SET @.strFilterCriteria = ' WHERE ' + @.strFilter + ' '
> SET @.strSimpleFilter = ' AND ' + @.strFilter + ' '
> END
> ELSE
> BEGIN
> SET @.strSimpleFilter = ''
> SET @.strFilterCriteria = ''
> END
> IF @.strSort IS NOT NULL AND @.strSort != ''
> SET @.strSortCriteria = ' ORDER BY ' + @.strSort + ' '
> ELSE
> SET @.strSortCriteria = ''
> IF @.strGroup IS NOT NULL AND @.strGroup != ''
> SET @.strGroupCriteria = 'GROUP BY' + @.strGroup + ' '
> ELSE
> SET @.strGroupCriteria = ''
> /* Now start doing the real work. */
> IF @.blnBringAllRecords = 1 -- Ignore paging and run a
> simple SELECT.
> BEGIN
> EXEC (
> 'SELECT ' + @.strFields + 'FROM' + @.strTables + @.strFilterCriteria +
> @.strGroupCriteria + @.strSortCriteria
> )
> END -- We had to bring all
> records.
> ELSE -- Bring only a
> particular page.
> BEGIN
> IF @.intPageNo = 1 -- In this case we can
> execute a more efficient
> -- query
> with no subqueries.
> EXEC (
> 'SELECT TOP' + @.strPageSize + ' ' + @.strFields + 'FROM' +
> @.strTables +
> @.strFilterCriteria + @.strGroupCriteria + @.strSortCriteria
> )
> ELSE -- Execute a structure of
> subqueries that brings the correct page.
> EXEC (
> 'SELECT' + @.strFields + 'FROM' + @.strTables + 'WHERE' + @.strPK +
> 'IN' + '
> (SELECT TOP' + @.strPageSize + ' ' + @.strPK + 'FROM' +
> @.strTables +
> ' WHERE' + @.strPK + 'NOT IN' + '
> (SELECT TOP' + @.strSkippedRows + ' ' + @.strPK + 'FROM' +
> @.strTables +
> @.strFilterCriteria + @.strGroupCriteria +
> @.strSortCriteria + ') ' +
> @.strSimpleFilter +
> @.strGroupCriteria +
> @.strSortCriteria + ') ' +
> @.strGroupCriteria +
> @.strSortCriteria
> )
> END -- We had to bring a
> particular page.
> /* If we need to return the recordcount: */
> IF @.blnGetRecordCount = 1
> IF @.strGroupCriteria != ''
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
> @.strTables + @.strFilterCriteria + @.strGroupCriteria + ') AS tbl
> (id)
> )
> ELSE
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM' + @.strTables +
> @.strFilterCriteria +
> @.strGroupCriteria
> )
> GO
>
>
>