I have 2 columns with data in different sequences in one table referencing a single column in different table.
I'm trying to learn SQL using SQLserver 2000.
I need some help Please!!
I'm having trouble creating a view that will give me the information that i need correctly.
I listed all the tables and the view that I tried but it's not working I dont think i have the view right.
Here is some info to help you understand what I'm trying to get:
Examples of the data that I'm having trouble with
only conscerns two of the tables
CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL, <---
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)
CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL, <Both ref above table
intAlternateDriverID INTEGER NOT NULL, <Both ref above table
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)
TDrivers Table has
intDriverID 1, 2, 3, 4, 5 and each id is associated with a name
1 = john
2 = mike
3 = sam
4 = jim
5 = tony
TScheduledRoutes Table
has column
intDriverID
and data is 1, 2, 3, 4, 5 that references TDrivers.intDriverID
and has column
intAlternateDriverID
and data is 5, 3, 1, 2, 4 that references TDrivers.intDriverID also
NOTICE the two have different sequence.
I need to get a select statement that would give me a list of
TScheduledRoutes.intDriverID full name
and their assciated alternate driver
TScheduledRoutes.intAlternateDriverID
output would give this as example
(intdriverID 1) john would have alt driverId 5 tony
I can't create a select statement that will give me both names at the same time.
Below is a list of the actual code and the view I cant get to do what I want it to and still keep the database in 3rd normal form.
Any suggestions would be greatly appreciated.
CREATE TABLE TRoutes
(
intRouteID INTEGER NOT NULL,
strRoute VARCHAR(30) NOT NULL,
strRouteDescription VARCHAR(50) NOT NULL,
CONSTRAINT TRoutes_PK PRIMARY KEY (intRouteID)
)
CREATE TABLE TBuses
(
intBusID INTEGER NOT NULL,
strBus VARCHAR(25) NOT NULL,
intCapacity INTEGER NOT NULL,
CONSTRAINT TBuses_PK PRIMARY KEY (intBusID)
)
CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL,
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)
CREATE TABLE TScheduleTimes
(
intScheduleTimeID INTEGER NOT NULL,
strScheduleTime DATETIME NOT NULL,
CONSTRAINT TScheduleTimes_PK PRIMARY KEY (intScheduleTimeID)
)
every column below is a foreign key to other tables
CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL,
intAlternateDriverID INTEGER NOT NULL,
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)
CREATE NONCLUSTERED INDEX TRoutes_NI ON TRoutes(strRoute)
CREATE NONCLUSTERED INDEX TBuses_NI ON TBuses(strBus)
CREATE NONCLUSTERED INDEX TDrivers_NI ON TDrivers (strLastName,strFirstName)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TBuses_TScheduledRoutes_FK
FOREIGN KEY (intBusID)REFERENCES TBuses(intBusID)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TRoutes_TScheduledRoutes_FK
FOREIGN KEY (intRouteID)REFERENCES TRoutes(intRouteID)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TDrivers_TScheduledRoutes_FK
FOREIGN KEY (intDriverID)REFERENCES TDrivers(intDriverID)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TADrivers_TScheduledRoutes_FK
FOREIGN KEY (intAlternateDriverID)REFERENCES TAltDrivers(intAltDriverID)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TScheduleTimes_TScheduledRoutes_FK
FOREIGN KEY (intScheduleTimeID)REFERENCES TScheduleTimes(intScheduleTimeID)
Here is what I tried but its not working. Is there better way to get the information I need and
keep the database in 3rd Normal form
CREATE VIEW V_SchedualedRoutes AS
SELECT TRoutes.strRoute,
TBuses.strBus,
(TDrivers.strLastName + ', '+ TDrivers.strFirstName)
AS strDriverFullName,
(SELECT TDrivers.strLastName + ', '
+ TDrivers.strFirstName)
FROM TDrivers
INNER JOIN TScheduledRoutes
ON TDrivers.intDriverID =
TScheduledRoutes.intDriverID
WHERE TScheduledRoutes.intAlternateDriverID=
TDrivers.intDriverI)
AS strAltDriFullName, TScheduleTimes.strScheduleTime
FROM TBuses
INNER JOIN TScheduledRoutes
ON TBuses.intBusID = TScheduledRoutes.intBusID
INNER JOIN TScheduleTimes
ON TScheduledRoutes.intScheduleTimeID =
TScheduleTimes.intScheduleTimeID
INNER JOIN TDrivers
ON TScheduledRoutes.intDriverID = TDrivers.intDriverID
AND TScheduledRoutes.intAlternateDriverID =
TDrivers.intDriverID
INNER JOIN TRoutes
ON TScheduledRoutes.intRouteID = TRoutes.intRouteIDI am having trouble following your query, but in essence it appears that the trouble you are having is knowing how to access the same table twice in a query - once for the main driver and once for the alternate driver. The solution is to use table aliases:
SELECT d1.strLastName MainDriver,
d1.strLastName AlternateDriver
FROM TScheduledRoutes sr
INNER JOIN TDrivers d1 ON sr.intDriverID = d1.intDriverID
INNER JOIN TDrivers d2 ON sr.intAlternateDriverID = d2.intDriverID
In your query, this bit looks to me like a syntax error:
(SELECT TDrivers.strLastName + ', ' + TDrivers.strFirstName) FROM TDrivers
... unless SQL Server has a very different SQL syntax that the one I know.|||Sorry about the confusion but thats it. Thank yousql
Showing posts with label learn. Show all posts
Showing posts with label learn. Show all posts
Friday, March 23, 2012
Wednesday, March 7, 2012
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
>
>
>
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
>
>
>
Monday, February 27, 2012
Help with new server registration
I am trying for the first time to learn what to do and how to use SQLServer. I am following instructions in Books on Line to make a New Server Registration. The instructions read as follows:
Connecting to Servers
The toolbar of the Registered Servers component has buttons for the Database Engine, Analysis Services, Reporting Services, SQL Server Mobile, and Integration Services. You can register any of these server types for convenient management. Try this exercise to register the AdventureWorks database.
To register the AdventureWorks database
On the Registered Servers toolbar, click Database Engine if necessary. (It may already be selected.)
Right-click Database Engine, point to New, and then click Server Registration. The New Server Registration dialog box opens.
In the Server name text box, type the name of your SQL Server instance.
In the Registered server name box, type AdventureWorks.
On the Connection Properties tab, in the Connect to database list, select AdventureWorks, and then click Save.
I did steps 1 and 2 no problem. At step 3, for server name I typed MARKSDESKTOP\SQLEXPRESS
At step 4 I typed: Adventureworks
At step 5 I went to Connection Properties and at the "Connect to database drop down box there were 2 choices: <default> or <browse server> (not Adventureworks). If I click on browse server, The browse server for Database window pops up but Adventureworks is not listed there either.
I did a search on my C drive and there are lots of Adventureworks files present so I must have downloaded the database OK.
Does anyone know where I go from here to connect to the Adventureworks database so I can continue with this tutorial?
Please help. Thanks
Mark
Hi,
did you attach the database on the registered server first. if you instaleld the databse via msi, the database is not automatically attached.
1. Register the server first (without any set database, it uses the default then)
2. Right click in the server explorer on "Connect" --> Object Explorer
3. Naviagte on the object explorer to Databases, right click and select Attach..
4. Click add and select the Adventureworks MDF file, click ok and you are done, you should see the adventureworks db now in user databases.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||do you mind telling me what books or website you are using to learn SQL? I am in the process of learning it myself. Thanks in advance.|||I can't imagine that you know less than me but so far I have been going through the following tutorial:
http://msdn2.microsoft.com/en-us/library/ms345318(SQL.90).aspx?notification_id=1721521&message_id=1721521
The amount of good it is doing is questionable. If you have any other suggestions, let me know.
Good luck.
Subscribe to:
Posts (Atom)