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
>
>
>

Help with new Synonym feature

I have found what I think is a good use for synonyms. However there's a
BUT I'm hoping someone can help me with.
I have several Great Plains databases for each of our overseas
operations. I use replication to pull these tables into one database
but I put them in seperate schemas, i.e. UK.IV00101, US.IV00101,
NO.IV00101.
I have various queries of the form;
If @.Subsidiary = 'UK'
Select * From UK.MyTable
If @.Subsidiary = 'US'
Select * From US.MyTable
If @.Subsidiary = 'NO'
Select * From NO.MyTable
or I have to use dynamic SQL or duplicate SP's for each subsidiary.
Now with synonyms I can do;
Set @.SQL = 'Create Synonym xMyTable For ~.MyTable;'
Set @.SQL = Replace(@.SQL, '~', @.Subsidiary)
Exec (@.SQL)
Select * From xMyTable
Drop Synonym xMyTable
Obviously in the real world we are talking about quite a few tables and
far more complex select statements, so this method seems to give some
performance advantage and a much neater script.
The downside is that I have to watch out for the script being run twice
and "cross-using" the same synonym. Now I could wrap it in a
transaction but this is purely for reporting so I'm trying to avoid
adding locking overheads.
What I'm after is "best practice" in this situation and comments on any
performance implications to what I'm trying.
What would have been nice would be a form of temporary synonym in the
same vain as temporary tables, i.e. valid just for that session and
valid only to that session.
Documentation on Synonyms is a little thin in BOL. Merely telling you
how to use rather than discussions on performance and what it's purpose
was for (which is ironic as I always complain that MS usually tell you
what something can do but not how!)
Regards
Chris> Now with synonyms I can do;
> Set @.SQL = 'Create Synonym xMyTable For ~.MyTable;'
> Set @.SQL = Replace(@.SQL, '~', @.Subsidiary)
> Exec (@.SQL)
> Select * From xMyTable
> Drop Synonym xMyTable
> Obviously in the real world we are talking about quite a few tables and
> far more complex select statements, so this method seems to give some
> performance advantage and a much neater script.
> The downside is that I have to watch out for the script being run twice
> and "cross-using" the same synonym. Now I could wrap it in a
> transaction but this is purely for reporting so I'm trying to avoid
> adding locking overheads.
Then don't use the SYNONYM at all, just say:
EXEC ('SELECT col_list FROM '+@.Subsidiary+'.MyTable')

> What I'm after is "best practice" in this situation
Dynamic SQL, in any flavor, is not best practice. To achieve best practice,
you will likely have to redesign, e.g. store all the data in a single table
and put things like 'NO' and 'US' in a column. Since these seem to be the
only variants of the data, there doesn't seem to be a compelling reason to
complicate all access to the data by splitting them out into umpteem tables.
The naming scheme "IV00101" seems suspect too, are there also tables like
IV00102, IV00103, etc.?
A|||Create a union view:-
CREATE VIEW dbo.ALL_MyTable AS
SELECT 'UK' AS Subsidiary , field1, field2, etc FROM UK.MyTable
UNION ALL SELECT 'US' AS Subsidiary , field1, field2, etc FROM US.MyTable
UNION ALL SELECT 'NO' AS Subsidiary , field1, field2, etc FROM NO.MyTable
Then you can filter using
SELECT * FROM dbo.ALL_MyTable WHERE Subsidiary IN( 'UK' , 'US' )
"Chris McGuigan" <mr.mcgoo@.gmail.com> wrote in message
news:e00UtwR6FHA.4076@.tk2msftngp13.phx.gbl...
> I have found what I think is a good use for synonyms. However there's a
> BUT I'm hoping someone can help me with.
> I have several Great Plains databases for each of our overseas
> operations. I use replication to pull these tables into one database
> but I put them in seperate schemas, i.e. UK.IV00101, US.IV00101,
> NO.IV00101.
> I have various queries of the form;
> If @.Subsidiary = 'UK'
> Select * From UK.MyTable
> If @.Subsidiary = 'US'
> Select * From US.MyTable
> If @.Subsidiary = 'NO'
> Select * From NO.MyTable
> or I have to use dynamic SQL or duplicate SP's for each subsidiary.
> Now with synonyms I can do;
> Set @.SQL = 'Create Synonym xMyTable For ~.MyTable;'
> Set @.SQL = Replace(@.SQL, '~', @.Subsidiary)
> Exec (@.SQL)
> Select * From xMyTable
> Drop Synonym xMyTable
> Obviously in the real world we are talking about quite a few tables and
> far more complex select statements, so this method seems to give some
> performance advantage and a much neater script.
> The downside is that I have to watch out for the script being run twice
> and "cross-using" the same synonym. Now I could wrap it in a
> transaction but this is purely for reporting so I'm trying to avoid
> adding locking overheads.
> What I'm after is "best practice" in this situation and comments on any
> performance implications to what I'm trying.
> What would have been nice would be a form of temporary synonym in the
> same vain as temporary tables, i.e. valid just for that session and
> valid only to that session.
> Documentation on Synonyms is a little thin in BOL. Merely telling you
> how to use rather than discussions on performance and what it's purpose
> was for (which is ironic as I always complain that MS usually tell you
> what something can do but not how!)
> --
> Regards
> Chris|||Chris McGuigan (mr.mcgoo@.gmail.com) writes:
> I have several Great Plains databases for each of our overseas
> operations. I use replication to pull these tables into one database
> but I put them in seperate schemas, i.e. UK.IV00101, US.IV00101,
> NO.IV00101.
> ...
> Now with synonyms I can do;
> Set @.SQL = 'Create Synonym xMyTable For ~.MyTable;'
> Set @.SQL = Replace(@.SQL, '~', @.Subsidiary)
> Exec (@.SQL)
> Select * From xMyTable
> Drop Synonym xMyTable
> Obviously in the real world we are talking about quite a few tables and
> far more complex select statements, so this method seems to give some
> performance advantage and a much neater script.
> The downside is that I have to watch out for the script being run twice
> and "cross-using" the same synonym. Now I could wrap it in a
> transaction but this is purely for reporting so I'm trying to avoid
> adding locking overheads.
>...
> What would have been nice would be a form of temporary synonym in the
> same vain as temporary tables, i.e. valid just for that session and
> valid only to that session.
That would indeed be a nice feature, but it falls on the same reason
as "SELECT * FROM @.tablename" - each table has unique set of statistics,
and needs to be compiled separately.
Had this been your own database, best practice had been as Aaron says,
make the subsidary a column in the table. I understand that with the
given setup, this is not really palatable. (Although I believe that it
is possible to set up replication to fill in the column for you. It may
not be worth the hassle though.)
So I agree with Rebecca York's suggestion: set up views that includes
all data. Be careful to use UNION ALL, then the optimzer should generate
a plan, so that if you have "WHERE subsidary = 'NO'", no other tables
are accessed. (Rebecca had UNION ALL in her post, but I wanted to stress
this point.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the reply.
The Exec is not an option, the real query is quite complex so currently
I have 4 sp's, one for each subsidiary. I do try to avoid dynamic SQL.
Ultimately, I will merge the 4 tables into one table but I have time
constraints and haven't worked out the best way to achieve this yet.
Synonyms looked like it could improve my code but delving deeper, don't
seem so useful.
The table naming is to do with Great Plains now a Microsoft product.
Regards
Chris
Aaron Bertrand [SQL Server MVP] wrote:

> Then don't use the SYNONYM at all, just say:
> EXEC ('SELECT col_list FROM '+@.Subsidiary+'.MyTable')
>
> Dynamic SQL, in any flavor, is not best practice. To achieve best
> practice, you will likely have to redesign, e.g. store all the data
> in a single table and put things like 'NO' and 'US' in a column.
> Since these seem to be the only variants of the data, there doesn't
> seem to be a compelling reason to complicate all access to the data
> by splitting them out into umpteem tables. The naming scheme
> "IV00101" seems suspect too, are there also tables like IV00102,
> IV00103, etc.?
> A|||Thanks Rebecca,
I hadn't thought of this as some of the tables can be relatively large
I assumed rightly or wrongly that I may get performance issues.
I'll look into this further.
Regards
Chris
Rebecca York wrote:
> Create a union view:-
> CREATE VIEW dbo.ALL_MyTable AS
> SELECT 'UK' AS Subsidiary , field1, field2, etc FROM UK.MyTable
> UNION ALL SELECT 'US' AS Subsidiary , field1, field2, etc FROM
> US.MyTable UNION ALL SELECT 'NO' AS Subsidiary , field1, field2, etc
> FROM NO.MyTable
> Then you can filter using
> SELECT * FROM dbo.ALL_MyTable WHERE Subsidiary IN( 'UK' , 'US' )
>
> "Chris McGuigan" <mr.mcgoo@.gmail.com> wrote in message
> news:e00UtwR6FHA.4076@.tk2msftngp13.phx.gbl...|||Thanks Erland,
I am aware of the Union All optimizer and always use it unless I
specifically don't want it.
I understand where you are coming from on the statistics.
So that leads me to wonder what purpose Synonyms can have other than to
give friendly names!
--
Regards
Chris
Erland Sommarskog wrote:

> Chris McGuigan (mr.mcgoo@.gmail.com) writes:
> That would indeed be a nice feature, but it falls on the same reason
> as "SELECT * FROM @.tablename" - each table has unique set of
> statistics, and needs to be compiled separately.
> Had this been your own database, best practice had been as Aaron says,
> make the subsidary a column in the table. I understand that with the
> given setup, this is not really palatable. (Although I believe that it
> is possible to set up replication to fill in the column for you. It
> may not be worth the hassle though.)
> So I agree with Rebecca York's suggestion: set up views that includes
> all data. Be careful to use UNION ALL, then the optimzer should
> generate a plan, so that if you have "WHERE subsidary = 'NO'", no
> other tables are accessed. (Rebecca had UNION ALL in her post, but I
> wanted to stress this point.)|||Chris McGuigan (mr.mcgoo@.gmail.com) writes:
> So that leads me to wonder what purpose Synonyms can have other than to
> give friendly names!
I will have to admit that is also the purpose I can see.
Well, I can see a few more scenarios. Here is one example: say that you
have an application that uses several databases. For all cross-database
access, you would set up synonyms. The idea is that if you need to set
up a second environment on the same server, you can achieve this by
updating all synonyms, but leaving the stored procedures untouched.
But for this purpose it would be more interesting to set up a synonym
for a database - which you can't.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx