Parameter Prompt with Wildcard

I'd like to create a report parameter to allow the user to enter only a
partial field using wildcard(s) in SRS. The list is lengthy and the user
does not always know the full name to enter.
Is this possible?
Thanks,
KarenVery much it can be done. Just you need to keep in mind about the single
quotes you are using for string paramters. eg '%userrequest' which you will
be passing the parameters to the "where" clause.
Amarnath
"Moving rpts from Access to Rptg Services" wrote:
> I'd like to create a report parameter to allow the user to enter only a
> partial field using wildcard(s) in SRS. The list is lengthy and the user
> does not always know the full name to enter.
> Is this possible?
> Thanks,
> Karen|||Thank you for our reply! in the where clause I currently have this:
WHERE (dbo.UP_Agents.AgentName = @.AgentName)
is this where I would add the wildcard? Or would I add in a separate
string? (sorry, i'm learning this as I go)
Thank you,
Karen
"Amarnath" wrote:
> Very much it can be done. Just you need to keep in mind about the single
> quotes you are using for string paramters. eg '%userrequest' which you will
> be passing the parameters to the "where" clause.
> Amarnath
> "Moving rpts from Access to Rptg Services" wrote:
> > I'd like to create a report parameter to allow the user to enter only a
> > partial field using wildcard(s) in SRS. The list is lengthy and the user
> > does not always know the full name to enter.
> >
> > Is this possible?
> >
> > Thanks,
> >
> > Karen|||AHA, this worked:
WHERE (dbo.UP_Agents.AgentName LIKE '%' + @.AgentName + '%')
Thanks for getting me started.
Karen
"Amarnath" wrote:
> Very much it can be done. Just you need to keep in mind about the single
> quotes you are using for string paramters. eg '%userrequest' which you will
> be passing the parameters to the "where" clause.
> Amarnath
> "Moving rpts from Access to Rptg Services" wrote:
> > I'd like to create a report parameter to allow the user to enter only a
> > partial field using wildcard(s) in SRS. The list is lengthy and the user
> > does not always know the full name to enter.
> >
> > Is this possible?
> >
> > Thanks,
> >
> > Karen

Parallel vs. serial plan

Hi,
What would be the advantages of lengthy report parallel vs.
serial execution, say on a 4-way SMP system, if the query
in itself is not able to produce sustained load of more
than 4 seconds on a single CPU of the system. I'm using MS
SQL Server 2000 on MS Windows 2000 with the lastest service
packs.
Many thanks,
OskOsk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
I would say that 4 seconds is a long time in the CPU world. It may not
be an issue if this is run off-hours, but during production hours, if
run many times, it could be more of an issue. Parallel executions
generally consume more CPU than their single-CPU brothers. It's just
more costly to manage parallel executions. So in your case, you are
potentially tying up one CPU out of four for four seconds, leaving the
other three to perform other tasks. With a parallel plan, depending on
how many CPUs SQL Server chooses to use, you'd end up tying up more CPU
for more than four CPU seconds, but the query could finish in 2.5
seconds.
Are you saying you have disabled parallel plans for this query using a
MAXDOP hint or have changed the corresponding server setting to prevent
parallel executions?
David Gugick
Imceda Software
www.imceda.com|||SQL-Server does not know how important a particular query is for you. It
doesn't discriminate queries that way. It just estimates (for each
query) whether executing the parallel plan is likely to be faster than
the serial plan. If it is, and the server is not too stressed, then it
will execute the parallel plan. Otherwise, it will execute the serial
plan.
However, there is a threshold below which a parallel plan will not be
considered. By default, this "cost threshold for parallelism" is set to
5. With this setting, SQL-Server will not consider a parallel plan if
the estimated execution time of the serial plan is less than 5 seconds.
Hope this helps,
Gert-Jan
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
> --
> Many thanks,
> Osk|||Yes, I have changed the 'max degree of parallelism' server
setting to 1. So as I understand it now, when the optimizer
thinks the query can benefit from executing in parllel at
the same time when the rest of CPUs are more or less idle
then, despite the parallel plan being more costly in terms
of CPU time, the query will return results faster. So I
need look at what kind of load is placed on the rest of
CPUs during the time the query runs to determine whether
there will be any benefit or parallelism.

>--Original Message--
>Osk wrote:
>I would say that 4 seconds is a long time in the CPU
world. It may not
>be an issue if this is run off-hours, but during
production hours, if
>run many times, it could be more of an issue. Parallel
executions
>generally consume more CPU than their single-CPU brothers.
It's just
>more costly to manage parallel executions. So in your
case, you are
>potentially tying up one CPU out of four for four seconds,
leaving the
>other three to perform other tasks. With a parallel plan,
depending on
>how many CPUs SQL Server chooses to use, you'd end up
tying up more CPU
>for more than four CPU seconds, but the query could finish
in 2.5
>seconds.
>Are you saying you have disabled parallel plans for this
query using a
>MAXDOP hint or have changed the corresponding server
setting to prevent
>parallel executions?
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Yes, I have changed the 'max degree of parallelism' server
> setting to 1. So as I understand it now, when the optimizer
> thinks the query can benefit from executing in parllel at
> the same time when the rest of CPUs are more or less idle
> then, despite the parallel plan being more costly in terms
> of CPU time, the query will return results faster. So I
> need look at what kind of load is placed on the rest of
> CPUs during the time the query runs to determine whether
> there will be any benefit or parallelism.
>
I thik you might be overthinking this a bit. Why have you decided to
change the MAX degree OF PARALLELISM on the server and not let SQL
Server manage parallel queries on its own? Occasionally, someone reports
an issue with a parallel plan which can be easily fixed with a MAXDOP
(1) in the query.
David Gugick
Imceda Software
www.imceda.com|||Well, that's how I understood it. If I'm wrong please
correct me.
I've set the server-wide MAXDOP to 1 because of two reasons:
1) to see whether placing the load of reports on a single
CPU will allow smaller queries have fulfilled their CPU
needs quicker than when they would have to wait for a
report to execute in parallel on all CPUs (although this
seems to work only in cases when the report doesn't hold
locks incompatible with other query's locks)
2) I can't provide hints at the T-SQL level yet
Thanks,
Osk

>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>I thik you might be overthinking this a bit. Why have you
decided to
>change the MAX degree OF PARALLELISM on the server and not
let SQL
>Server manage parallel queries on its own? Occasionally,
someone reports
>an issue with a parallel plan which can be easily fixed
with a MAXDOP
>(1) in the query.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||Osk wrote:
> Well, that's how I understood it. If I'm wrong please
> correct me.
> I've set the server-wide MAXDOP to 1 because of two reasons:
> 1) to see whether placing the load of reports on a single
> CPU will allow smaller queries have fulfilled their CPU
> needs quicker than when they would have to wait for a
> report to execute in parallel on all CPUs (although this
> seems to work only in cases when the report doesn't hold
> locks incompatible with other query's locks)
> 2) I can't provide hints at the T-SQL level yet
>
MAXDOP at the server level does not mean that reports are only going to
run on a single CPU. Unless what you're saying is that only one report
can run at a time and because of that it will only run on a single CPU.
MAXDOP 1 just means SQL Server will only use a single CPU for a query,
but the CPU used could be any available (maybe you already understood
that). Obviously, if 4 reports were executed simultaneously and you had
4 CPUs on the server, they could be running on different CPUs.
If you have concerns, I guess you could leave the option on, but I would
suggest you test with the default settings as well.
David Gugick
Imceda Software
www.imceda.comsql

Parallel vs. serial plan

Hi,
What would be the advantages of lengthy report parallel vs.
serial execution, say on a 4-way SMP system, if the query
in itself is not able to produce sustained load of more
than 4 seconds on a single CPU of the system. I'm using MS
SQL Server 2000 on MS Windows 2000 with the lastest service
packs.
Many thanks,
Osk
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
I would say that 4 seconds is a long time in the CPU world. It may not
be an issue if this is run off-hours, but during production hours, if
run many times, it could be more of an issue. Parallel executions
generally consume more CPU than their single-CPU brothers. It's just
more costly to manage parallel executions. So in your case, you are
potentially tying up one CPU out of four for four seconds, leaving the
other three to perform other tasks. With a parallel plan, depending on
how many CPUs SQL Server chooses to use, you'd end up tying up more CPU
for more than four CPU seconds, but the query could finish in 2.5
seconds.
Are you saying you have disabled parallel plans for this query using a
MAXDOP hint or have changed the corresponding server setting to prevent
parallel executions?
David Gugick
Imceda Software
www.imceda.com
|||SQL-Server does not know how important a particular query is for you. It
doesn't discriminate queries that way. It just estimates (for each
query) whether executing the parallel plan is likely to be faster than
the serial plan. If it is, and the server is not too stressed, then it
will execute the parallel plan. Otherwise, it will execute the serial
plan.
However, there is a threshold below which a parallel plan will not be
considered. By default, this "cost threshold for parallelism" is set to
5. With this setting, SQL-Server will not consider a parallel plan if
the estimated execution time of the serial plan is less than 5 seconds.
Hope this helps,
Gert-Jan
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
> --
> Many thanks,
> Osk
|||Yes, I have changed the 'max degree of parallelism' server
setting to 1. So as I understand it now, when the optimizer
thinks the query can benefit from executing in parllel at
the same time when the rest of CPUs are more or less idle
then, despite the parallel plan being more costly in terms
of CPU time, the query will return results faster. So I
need look at what kind of load is placed on the rest of
CPUs during the time the query runs to determine whether
there will be any benefit or parallelism.

>--Original Message--
>Osk wrote:
>I would say that 4 seconds is a long time in the CPU
world. It may not
>be an issue if this is run off-hours, but during
production hours, if
>run many times, it could be more of an issue. Parallel
executions
>generally consume more CPU than their single-CPU brothers.
It's just
>more costly to manage parallel executions. So in your
case, you are
>potentially tying up one CPU out of four for four seconds,
leaving the
>other three to perform other tasks. With a parallel plan,
depending on
>how many CPUs SQL Server chooses to use, you'd end up
tying up more CPU
>for more than four CPU seconds, but the query could finish
in 2.5
>seconds.
>Are you saying you have disabled parallel plans for this
query using a
>MAXDOP hint or have changed the corresponding server
setting to prevent
>parallel executions?
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>
|||anonymous@.discussions.microsoft.com wrote:
> Yes, I have changed the 'max degree of parallelism' server
> setting to 1. So as I understand it now, when the optimizer
> thinks the query can benefit from executing in parllel at
> the same time when the rest of CPUs are more or less idle
> then, despite the parallel plan being more costly in terms
> of CPU time, the query will return results faster. So I
> need look at what kind of load is placed on the rest of
> CPUs during the time the query runs to determine whether
> there will be any benefit or parallelism.
>
I thik you might be overthinking this a bit. Why have you decided to
change the MAX DEGREE OF PARALLELISM on the server and not let SQL
Server manage parallel queries on its own? Occasionally, someone reports
an issue with a parallel plan which can be easily fixed with a MAXDOP
(1) in the query.
David Gugick
Imceda Software
www.imceda.com
|||Well, that's how I understood it. If I'm wrong please
correct me.
I've set the server-wide MAXDOP to 1 because of two reasons:
1) to see whether placing the load of reports on a single
CPU will allow smaller queries have fulfilled their CPU
needs quicker than when they would have to wait for a
report to execute in parallel on all CPUs (although this
seems to work only in cases when the report doesn't hold
locks incompatible with other query's locks)
2) I can't provide hints at the T-SQL level yet
Thanks,
Osk

>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>I thik you might be overthinking this a bit. Why have you
decided to
>change the MAX DEGREE OF PARALLELISM on the server and not
let SQL
>Server manage parallel queries on its own? Occasionally,
someone reports
>an issue with a parallel plan which can be easily fixed
with a MAXDOP
>(1) in the query.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>
|||Osk wrote:
> Well, that's how I understood it. If I'm wrong please
> correct me.
> I've set the server-wide MAXDOP to 1 because of two reasons:
> 1) to see whether placing the load of reports on a single
> CPU will allow smaller queries have fulfilled their CPU
> needs quicker than when they would have to wait for a
> report to execute in parallel on all CPUs (although this
> seems to work only in cases when the report doesn't hold
> locks incompatible with other query's locks)
> 2) I can't provide hints at the T-SQL level yet
>
MAXDOP at the server level does not mean that reports are only going to
run on a single CPU. Unless what you're saying is that only one report
can run at a time and because of that it will only run on a single CPU.
MAXDOP 1 just means SQL Server will only use a single CPU for a query,
but the CPU used could be any available (maybe you already understood
that). Obviously, if 4 reports were executed simultaneously and you had
4 CPUs on the server, they could be running on different CPUs.
If you have concerns, I guess you could leave the option on, but I would
suggest you test with the default settings as well.
David Gugick
Imceda Software
www.imceda.com

Parallel vs. serial plan

Hi,
What would be the advantages of lengthy report parallel vs.
serial execution, say on a 4-way SMP system, if the query
in itself is not able to produce sustained load of more
than 4 seconds on a single CPU of the system. I'm using MS
SQL Server 2000 on MS Windows 2000 with the lastest service
packs.
--
Many thanks,
OskOsk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
I would say that 4 seconds is a long time in the CPU world. It may not
be an issue if this is run off-hours, but during production hours, if
run many times, it could be more of an issue. Parallel executions
generally consume more CPU than their single-CPU brothers. It's just
more costly to manage parallel executions. So in your case, you are
potentially tying up one CPU out of four for four seconds, leaving the
other three to perform other tasks. With a parallel plan, depending on
how many CPUs SQL Server chooses to use, you'd end up tying up more CPU
for more than four CPU seconds, but the query could finish in 2.5
seconds.
Are you saying you have disabled parallel plans for this query using a
MAXDOP hint or have changed the corresponding server setting to prevent
parallel executions?
--
David Gugick
Imceda Software
www.imceda.com|||SQL-Server does not know how important a particular query is for you. It
doesn't discriminate queries that way. It just estimates (for each
query) whether executing the parallel plan is likely to be faster than
the serial plan. If it is, and the server is not too stressed, then it
will execute the parallel plan. Otherwise, it will execute the serial
plan.
However, there is a threshold below which a parallel plan will not be
considered. By default, this "cost threshold for parallelism" is set to
5. With this setting, SQL-Server will not consider a parallel plan if
the estimated execution time of the serial plan is less than 5 seconds.
Hope this helps,
Gert-Jan
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
> --
> Many thanks,
> Osk|||Yes, I have changed the 'max degree of parallelism' server
setting to 1. So as I understand it now, when the optimizer
thinks the query can benefit from executing in parllel at
the same time when the rest of CPUs are more or less idle
then, despite the parallel plan being more costly in terms
of CPU time, the query will return results faster. So I
need look at what kind of load is placed on the rest of
CPUs during the time the query runs to determine whether
there will be any benefit or parallelism.
>--Original Message--
>Osk wrote:
>> Hi,
>> What would be the advantages of lengthy report parallel vs.
>> serial execution, say on a 4-way SMP system, if the query
>> in itself is not able to produce sustained load of more
>> than 4 seconds on a single CPU of the system. I'm using MS
>> SQL Server 2000 on MS Windows 2000 with the lastest service
>> packs.
>I would say that 4 seconds is a long time in the CPU
world. It may not
>be an issue if this is run off-hours, but during
production hours, if
>run many times, it could be more of an issue. Parallel
executions
>generally consume more CPU than their single-CPU brothers.
It's just
>more costly to manage parallel executions. So in your
case, you are
>potentially tying up one CPU out of four for four seconds,
leaving the
>other three to perform other tasks. With a parallel plan,
depending on
>how many CPUs SQL Server chooses to use, you'd end up
tying up more CPU
>for more than four CPU seconds, but the query could finish
in 2.5
>seconds.
>Are you saying you have disabled parallel plans for this
query using a
>MAXDOP hint or have changed the corresponding server
setting to prevent
>parallel executions?
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Yes, I have changed the 'max degree of parallelism' server
> setting to 1. So as I understand it now, when the optimizer
> thinks the query can benefit from executing in parllel at
> the same time when the rest of CPUs are more or less idle
> then, despite the parallel plan being more costly in terms
> of CPU time, the query will return results faster. So I
> need look at what kind of load is placed on the rest of
> CPUs during the time the query runs to determine whether
> there will be any benefit or parallelism.
>
I thik you might be overthinking this a bit. Why have you decided to
change the MAX DEGREE OF PARALLELISM on the server and not let SQL
Server manage parallel queries on its own? Occasionally, someone reports
an issue with a parallel plan which can be easily fixed with a MAXDOP
(1) in the query.
--
David Gugick
Imceda Software
www.imceda.com|||Well, that's how I understood it. If I'm wrong please
correct me.
I've set the server-wide MAXDOP to 1 because of two reasons:
1) to see whether placing the load of reports on a single
CPU will allow smaller queries have fulfilled their CPU
needs quicker than when they would have to wait for a
report to execute in parallel on all CPUs (although this
seems to work only in cases when the report doesn't hold
locks incompatible with other query's locks)
2) I can't provide hints at the T-SQL level yet
--
Thanks,
Osk
>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>> Yes, I have changed the 'max degree of parallelism' server
>> setting to 1. So as I understand it now, when the optimizer
>> thinks the query can benefit from executing in parllel at
>> the same time when the rest of CPUs are more or less idle
>> then, despite the parallel plan being more costly in terms
>> of CPU time, the query will return results faster. So I
>> need look at what kind of load is placed on the rest of
>> CPUs during the time the query runs to determine whether
>> there will be any benefit or parallelism.
>I thik you might be overthinking this a bit. Why have you
decided to
>change the MAX DEGREE OF PARALLELISM on the server and not
let SQL
>Server manage parallel queries on its own? Occasionally,
someone reports
>an issue with a parallel plan which can be easily fixed
with a MAXDOP
>(1) in the query.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||Osk wrote:
> Well, that's how I understood it. If I'm wrong please
> correct me.
> I've set the server-wide MAXDOP to 1 because of two reasons:
> 1) to see whether placing the load of reports on a single
> CPU will allow smaller queries have fulfilled their CPU
> needs quicker than when they would have to wait for a
> report to execute in parallel on all CPUs (although this
> seems to work only in cases when the report doesn't hold
> locks incompatible with other query's locks)
> 2) I can't provide hints at the T-SQL level yet
>
MAXDOP at the server level does not mean that reports are only going to
run on a single CPU. Unless what you're saying is that only one report
can run at a time and because of that it will only run on a single CPU.
MAXDOP 1 just means SQL Server will only use a single CPU for a query,
but the CPU used could be any available (maybe you already understood
that). Obviously, if 4 reports were executed simultaneously and you had
4 CPUs on the server, they could be running on different CPUs.
If you have concerns, I guess you could leave the option on, but I would
suggest you test with the default settings as well.
David Gugick
Imceda Software
www.imceda.com

Paging and Sorting With RowNum()

I apologize in advance, but this post might get somewhat lengthy.

I'm new to the whole pagiong and sorting in SQL Server 2005, and I'm trying to get my SQL to perform in a certain way but can't seem to nail it just down. Hopefully someone can provide some insight or direction. Here's the scoop:

The gui sorts on any column chosen. For example, there's USER, ADDRESS, CITY, STATE, ZIP. The gui allows you to choose how many rows you wish to display per page. If there are 500 rows that meet the search criteria and you choose five pages, there should be 100 records per page. Here's the code:

INSERT INTO #RESULTS

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description

, AC.AccountProvider

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentId,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentId = PY.AccountPaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

AND ((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

ORDER BY AccountPaymentID

--GET A COUNT OF THE ROWS SELECTED

SELECT @.TotalRows = Count(*)

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.PaymentId,

ROW_NUMBER() OVER(ORDER BY PY.PaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.PaymentId = PY.PaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE

((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

SET @.ORDERBY = ' ORDER BY ' + @.SORT --END

--CASE WHEN @.Sort IS NULL THEN '' ELSE

EXEC('SELECT * FROM #RESULTS ' + @.ORDERBY)

--SET @.TOPSQL = 'SELECT TOP ' + Convert(VarChar,@.PageSize) + '* FROM #RESULTS ' + @.ORDERBY

--SELECT @.TOPSQL

--EXEC (@.TOPSQL)

SET @.PAGES = Round(@.totalRows / @.PageSize,0,1) + CASE WHEN @.TotalRows % @.PageSize = 0 THEN 0 ELSE 1 END

-- Return Total number of pages and Total number of Rows

SELECT @.PAGES AS PageCount,

@.TOTALROWS AS TotalRecords

I get back the rows I'm expecting and it looks fine. What I can't get to happen is the proper sort.



I get back records 1014 records - 1001 through 2014. I choose to display 400 records per page, so ther will be 3 pages total (1001 through 1400 on page 1, 1401 through 1800 on page 2, and 1801 through 2014 on page 3. All the records are sorted by RECORD NUMBER (1000, 1001, etc.)



What I would like to do is when I choose to sort on the column (ASC or DESC),

1.) The entire record set is esssentially retrieved again,

2.) The record set is resorted in the proper order

3.) The record set is redisplayed.

For example, if I'm on Page 2, and I choose to sort in DESCending order, Page 1 would then have records 2014 through 1615, Page 2 would display 1614 through 1215, and page 3 would have 1214 through 1001. Since I was already on Page 2, I would be seeing Page 2 with the new sort. Now when I resort , it just sorts the records on the individual pages, not the entire result set.

Hopefully this all made sense...!

If anyone has any advice or insight, please don't hesitate!

Thank You!!I'm just a grunt query writer, so I'll post a method that the smarter folks here can upstage with the better solution. But I know this works since I had just that issue today.

I'd write a View containing the basic query, then write a Stored Procedure that receives a Sort code. Then I would put a series of IF statements and order the results accordingly.

Since you have a lot of columns; if the data isn't write/update intensive and the results are often large, consider indexing the view. I *think* sorting by an indexed column is faster, but I'm going to duck anyway for the responses that one will give.

My thinking is that since it's all hard coded into the stored procedure it'll all get compiled just the once (as opposed to Dynamic SQL).

So; something like this:

Create Procedure sp_MyData (@.SortCode int)

if @.SortCode = 1
select * from qryMyView
order by qryMyView.SortColumn1

if @.SortCode = 2
select * from qryMyView
order by qryMyView.SortColumn2

GO

Obviously; replace "Sort Code" with actual column names for clarity if you wish.|||That's not gonna work...

The reason there is dynamic SQL is because you never know what someone is going to be passing in...

Paging and Sorting Using ROWNUM()

I apologize in advance, but this post might get somewhat lengthy.

I'm new to the whole pagiong and sorting in SQL Server 2005, and I'm trying to get my SQL to perform in a certain way but can't seem to nail it just down. Hopefully someone can provide some insight or direction. Here's the scoop:

The gui sorts on any column chosen. For example, there's USER, ADDRESS, CITY, STATE, ZIP. The gui allows you to choose how many rows you wish to display per page. If there are 500 rows that meet the search criteria and you choose five pages, there should be 100 records per page. Here's the code:

Code Snippet

INSERT INTO #RESULTS

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description

, AC.AccountProvider

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentId,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentId = PY.AccountPaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

AND ((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

ORDER BY AccountPaymentID

--GET A COUNT OF THE ROWS SELECTED

SELECT @.TotalRows = Count(*)

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.PaymentId,

ROW_NUMBER() OVER(ORDER BY PY.PaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.PaymentId = PY.PaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE

((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

SET @.ORDERBY = ' ORDER BY ' + @.SORT --END

--CASE WHEN @.Sort IS NULL THEN '' ELSE

EXEC('SELECT * FROM #RESULTS ' + @.ORDERBY)

--SET @.TOPSQL = 'SELECT TOP ' + Convert(VarChar,@.PageSize) + '* FROM #RESULTS ' + @.ORDERBY

--SELECT @.TOPSQL

--EXEC (@.TOPSQL)

SET @.PAGES = Round(@.totalRows / @.PageSize,0,1) + CASE WHEN @.TotalRows % @.PageSize = 0 THEN 0 ELSE 1 END

-- Return Total number of pages and Total number of Rows

SELECT @.PAGES AS PageCount,

@.TOTALROWS AS TotalRecords

I get back the rows I'm expecting and it looks fine. What I can't get to happen is the proper sort.

I get back records 1014 records - 1001 through 2014. I choose to display 400 records per page, so ther will be 3 pages total (1001 through 1400 on page 1, 1401 through 1800 on page 2, and 1801 through 2014 on page 3. All the records are sorted by RECORD NUMBER (1000, 1001, etc.)

What I would like to do is when I choose to sort on the column (ASC or DESC),

1.) The entire record set is esssentially retrieved again,

2.) The record set is resorted in the proper order

3.) The record set is redisplayed.

For example, if I'm on Page 2, and I choose to sort in DESCending order, Page 1 would then have records 2014 through 1615, Page 2 would display 1614 through 1215, and page 3 would have 1214 through 1001. Since I was already on Page 2, I would be seeing Page 2 with the new sort. Now when I resort , it just sorts the records on the individual pages, not the entire result set.

Hopefully this all made sense...!

If anyone has any advice or insight, please don't hesitate!

Thank You!!

If you want to have the paging for performance boost, then you have to follow these rules,

1. You should identify the unique value which identify the each row

2. These Unique columns should be sorted

(Example)

Using Sysobjects fetch 20 records per page

Code Snippet

Create Proc FetchPagingData

(

@.RowsPerPage int,

@.LastRow varchar(100)

)

as

Begin

Select Top(@.RowsPerPage) * into #T from Sysobjects

Where name > @.LastRow or @.LastRow is NULL

Order By Name;

--Data for Page

Select * from #T

--Last Row data for next page @.LastRow param

Select Top 1 Name from #T Order By Name Desc;

--Total Records & page

Select Count(*) as TotalRows,Count(*)/@.RowsPerPage as TotalPages From Sysobjects

End

go

--Page 1

Exec FetchPagingData 20, null

--Page 2

Exec FetchPagingData 20, 'COLUMN_DOMAIN_USAGE'

--Page 3

Exec FetchPagingData 20, 'database_permissions'

If you can’t satisfy above rule then you have to use the Row_Number(); but it wont give any performance boost. If you use .NET better you can cache it on the dataset & reuse the dataset for each page.

|||This isn't going to work...I need to use ROW_NUMBER()....

|||

If you use SQL Server 2005 then the following query might help you – it wont increase the performance, but you may cut down the network trafic.

Code Snippet

Create Proc FetchPagingData

(

@.RowsPerPage int,

@.Page int

)

as

Begin

;With CTE

as

(

Select * , Row_Number() Over(order By name) RowNumfrom Sysobjects

)

Select * from CTE Where RowNum >(@.Page-1) * @.RowsPerPage

and RowNum <= (@.Page) * @.RowsPerPage Order By Name

--Total Records & page

Select Count(*) as TotalRows,Count(*)/@.RowsPerPage as TotalPages From Sysobjects

End

go

--Page 1

Exec FetchPagingData 20, 1

--Page 2

Exec FetchPagingData 20, 2

--Page 3

Exec FetchPagingData 20, 3

|||

Maybe I'm being dense, but I'm not seeing how this addresses my problem. My issue is that I need to order a complete record set, and then break that entire set down in to pages.

In the FetchPagingData procedure, you're passing in the number of pages (which I have) and some @.Page variable, which I have no idea what that is.

Also, that proc is going against a static table, not a temp table. ANd there's no way to know how many pages I have until I perform the actual query...

This is how I it working but can't quite put my finger on how to make it work:

Let's say I'm sorting/paging on NAME. I run a query based on NAME which INSERT's the records into my temp table in ASCending order. There are 1014 records. The default number of records per page is 1000, thus there will be 2 pages in this query. The first page has records A. Dan Ryals through William Green - row numbers 1 through 1000. The send page has records William Pallister through Zhariff Hulagana - these are numbered 1 through 14.

Now let's say I choose to sort this by NAME DESCending, the behavior I expect to see is the ENTIRE result being resorted in DESCending order, thus invalidating the old row numbers. The first page should have records Zhariff Hulagana through the first 1000, and page 2 should have the next 14 records, the last one being A. Dan Ryals.

If I insert every record into my temp table and then try to select just the chosen rows I get an error that RowNum is undefined. Here's my snippet:

CREATE TABLE #RESULTS (

SEQ INT )

EXEC(@.SQL)

--IF @.SORT IS NULL EXECUTE COMPLETE SEARCH

IF @.COLUMN_NAME IS NULL OR @.COLUMN_NAME = 'AccountPAymentID'

BEGIN

INSERT INTO #RESULTS

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description

, AC.AccountProvider

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentId,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentId = PY.AccountPaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

--WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

WHERE ((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

ORDER BY AccountPaymentID

--GET A COUNT OF THE ROWS SELECTED

SELECT @.TotalRows = Count(*)

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.PaymentId,

ROW_NUMBER() OVER(ORDER BY PY.PaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.PaymentId = PY.PaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE

((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

SET @.OrderBy = CASE WHEN @.Sort IS NULL THEN '' ELSE ' ORDER BY ' + @.SORT END

--SELECT @.ORDERBY

--EXEC('SELECT * FROM #RESULTS ' + @.ORDERBY)

SELECT * FROM #RESULTS WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

SET @.PAGES = Round(@.totalRows / @.PageSize,0,1) + CASE WHEN @.TotalRows % @.PageSize = 0 THEN 0 ELSE 1 END

-- Return Total number of pages and Total number of Rows

SELECT @.PAGES AS PageCount,

@.TOTALROWS AS TotalRecords

|||

Ok. I got your point.

Let me clarify here,

You will pass the number of records on each page or number of pages you required.

Once the input passed you have to identify the each row's page number.

If you use SQL Server 2005 its very simple, we have to change the previous query slightly.

With Assumption1: Passing Number of Record for each page..

Code Snippet

Create Proc FetchDataWithPaging_ForNoOfRowPerPage

(@.RowsPerPage int)

as

Begin

;With CTE

as

(

Select Name,Id,XType , Row_Number() Over(order By name) RowNumfrom Sysobjects

)

Select *, ((RowNum-1)/@.RowsPerPage) + 1 as Page from CTE

Order By Name

--Total Recodrs & pages

Select Count(*) as TotalRows,Count(*)/@.RowsPerPage as TotalPages From Sysobjects

End

go

Exec FetchDataWithPaging_ForNoOfRowPerPage 1000

With Assumption 2 -- Passing Required Pages,

Code Snippet

Create Proc FetchDataWithPaging_ForNoOfPage

(@.RequiredPages int)

as

Begin

Declare @.TotalRecords as Int;

Declare @.RowsPerPage as Int;

Select Name,Id,XType into #t from Sysobjects;

Select @.TotalRecords = Count(*) From #t

Select @.RowsPerPage = Round(Cast(@.TotalRecords as float)/Cast(@.RequiredPages as float),0)

;With CTE

as

(

Select * , Row_Number() Over(order By name) RowNumfrom #T

)

Select *, ((RowNum-1)/@.RowsPerPage) + 1 as Page from CTE

Order By Name

--Total Recodrs & pages

Select Count(*) as TotalRows,Count(*)/@.RowsPerPage as TotalPages, @.RowsPerPage RowsPerPage From #T

End

go

Exec FetchDataWithPaging_ForNoOfPage 10

|||

So last dumb question:

So would I take my entire SELECT query, including the JOIN's and stick it in that CTE piece?

Is there another way, because frankly I am utterly confused as to how I would convert this query:

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description

, AC.AccountProvider

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentId,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentId = PY.AccountPaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

--WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

WHERE ((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

ORDER BY AccountPaymentID

...into your query. Also, mine is already contained within a stored procedure.

I apologize for my paging ignorance. I'm not new to SQL Server, but I am new to the whole sorting thing via SQL Server so slowly picking it up. Is there any way to take what I already have and just tweak it a bit. The with CTE has me kind of baffled...

|||

Yes. You can do that. The query result will be act as simple table (only for writing query) on CTE.

|||

Last one, I swear...

I've altered my query to utilize a CTE as:

Code Snippet

BEGIN

DECLARE @.PageSize INT

SET @.PAgeSize = 1000

;

WITH CTE

AS

(

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description AS AccountDescription

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentID,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentID) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentID = PY.AccountPaymentID)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

)

Select *, ((RowNum-1)/@.PageSize) + 1 as Page from CTE

end

When I run the query, I'm getting the following error:

Msg 207, Level 16, State 1, Line 67

Invalid column name 'RowNum'.

Am I missing something?|||

You have to expose the Rownum in the SELECT clause of the toppermost query in the CTE:

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

....

, PE.clm_no

, CM.clmnt_idno

, SQ.RowNum

FROM ...

I think SQ is the right alias, but it is the right gist.

Paging and Sorting Using ROWNUM()

I apologize in advance, but this post might get somewhat lengthy.

I'm new to the whole pagiong and sorting in SQL Server 2005, and I'm trying to get my SQL to perform in a certain way but can't seem to nail it just down. Hopefully someone can provide some insight or direction. Here's the scoop:

The gui sorts on any column chosen. For example, there's USER, ADDRESS, CITY, STATE, ZIP. The gui allows you to choose how many rows you wish to display per page. If there are 500 rows that meet the search criteria and you choose five pages, there should be 100 records per page. Here's the code:

Code Snippet

INSERT INTO #RESULTS

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description

, AC.AccountProvider

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentId,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentId = PY.AccountPaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

AND ((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

ORDER BY AccountPaymentID

--GET A COUNT OF THE ROWS SELECTED

SELECT @.TotalRows = Count(*)

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.PaymentId,

ROW_NUMBER() OVER(ORDER BY PY.PaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.PaymentId = PY.PaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE

((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

SET @.ORDERBY = ' ORDER BY ' + @.SORT --END

--CASE WHEN @.Sort IS NULL THEN '' ELSE

EXEC('SELECT * FROM #RESULTS ' + @.ORDERBY)

--SET @.TOPSQL = 'SELECT TOP ' + Convert(VarChar,@.PageSize) + '* FROM #RESULTS ' + @.ORDERBY

--SELECT @.TOPSQL

--EXEC (@.TOPSQL)

SET @.PAGES = Round(@.totalRows / @.PageSize,0,1) + CASE WHEN @.TotalRows % @.PageSize = 0 THEN 0 ELSE 1 END

-- Return Total number of pages and Total number of Rows

SELECT @.PAGES AS PageCount,

@.TOTALROWS AS TotalRecords

I get back the rows I'm expecting and it looks fine. What I can't get to happen is the proper sort.

I get back records 1014 records - 1001 through 2014. I choose to display 400 records per page, so ther will be 3 pages total (1001 through 1400 on page 1, 1401 through 1800 on page 2, and 1801 through 2014 on page 3. All the records are sorted by RECORD NUMBER (1000, 1001, etc.)

What I would like to do is when I choose to sort on the column (ASC or DESC),

1.) The entire record set is esssentially retrieved again,

2.) The record set is resorted in the proper order

3.) The record set is redisplayed.

For example, if I'm on Page 2, and I choose to sort in DESCending order, Page 1 would then have records 2014 through 1615, Page 2 would display 1614 through 1215, and page 3 would have 1214 through 1001. Since I was already on Page 2, I would be seeing Page 2 with the new sort. Now when I resort , it just sorts the records on the individual pages, not the entire result set.

Hopefully this all made sense...!

If anyone has any advice or insight, please don't hesitate!

Thank You!!

If you want to have the paging for performance boost, then you have to follow these rules,

1. You should identify the unique value which identify the each row

2. These Unique columns should be sorted

(Example)

Using Sysobjects fetch 20 records per page

Code Snippet

Create Proc FetchPagingData

(

@.RowsPerPage int,

@.LastRow varchar(100)

)

as

Begin

Select Top(@.RowsPerPage) * into #T from Sysobjects

Where name > @.LastRow or @.LastRow is NULL

Order By Name;

--Data for Page

Select * from #T

--Last Row data for next page @.LastRow param

Select Top 1 Name from #T Order By Name Desc;

--Total Records & page

Select Count(*) as TotalRows,Count(*)/@.RowsPerPage as TotalPages From Sysobjects

End

go

--Page 1

Exec FetchPagingData 20, null

--Page 2

Exec FetchPagingData 20, 'COLUMN_DOMAIN_USAGE'

--Page 3

Exec FetchPagingData 20, 'database_permissions'

If you can’t satisfy above rule then you have to use the Row_Number(); but it wont give any performance boost. If you use .NET better you can cache it on the dataset & reuse the dataset for each page.

|||This isn't going to work...I need to use ROW_NUMBER()....|||

If you use SQL Server 2005 then the following query might help you – it wont increase the performance, but you may cut down the network trafic.

Code Snippet

Create Proc FetchPagingData

(

@.RowsPerPage int,

@.Page int

)

as

Begin

;With CTE

as

(

Select * , Row_Number() Over(order By name) RowNumfrom Sysobjects

)

Select * from CTE Where RowNum >(@.Page-1) * @.RowsPerPage

and RowNum <= (@.Page) * @.RowsPerPage Order By Name

--Total Records & page

Select Count(*) as TotalRows,Count(*)/@.RowsPerPage as TotalPages From Sysobjects

End

go

--Page 1

Exec FetchPagingData 20, 1

--Page 2

Exec FetchPagingData 20, 2

--Page 3

Exec FetchPagingData 20, 3

|||

Maybe I'm being dense, but I'm not seeing how this addresses my problem. My issue is that I need to order a complete record set, and then break that entire set down in to pages.

In the FetchPagingData procedure, you're passing in the number of pages (which I have) and some @.Page variable, which I have no idea what that is.

Also, that proc is going against a static table, not a temp table. ANd there's no way to know how many pages I have until I perform the actual query...

This is how I it working but can't quite put my finger on how to make it work:

Let's say I'm sorting/paging on NAME. I run a query based on NAME which INSERT's the records into my temp table in ASCending order. There are 1014 records. The default number of records per page is 1000, thus there will be 2 pages in this query. The first page has records A. Dan Ryals through William Green - row numbers 1 through 1000. The send page has records William Pallister through Zhariff Hulagana - these are numbered 1 through 14.

Now let's say I choose to sort this by NAME DESCending, the behavior I expect to see is the ENTIRE result being resorted in DESCending order, thus invalidating the old row numbers. The first page should have records Zhariff Hulagana through the first 1000, and page 2 should have the next 14 records, the last one being A. Dan Ryals.

If I insert every record into my temp table and then try to select just the chosen rows I get an error that RowNum is undefined. Here's my snippet:

CREATE TABLE #RESULTS (

SEQ INT )

EXEC(@.SQL)

--IF @.SORT IS NULL EXECUTE COMPLETE SEARCH

IF @.COLUMN_NAME IS NULL OR @.COLUMN_NAME = 'AccountPAymentID'

BEGIN

INSERT INTO #RESULTS

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description

, AC.AccountProvider

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentId,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentId = PY.AccountPaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

--WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

WHERE ((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

ORDER BY AccountPaymentID

--GET A COUNT OF THE ROWS SELECTED

SELECT @.TotalRows = Count(*)

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.PaymentId,

ROW_NUMBER() OVER(ORDER BY PY.PaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.PaymentId = PY.PaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE

((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

SET @.OrderBy = CASE WHEN @.Sort IS NULL THEN '' ELSE ' ORDER BY ' + @.SORT END

--SELECT @.ORDERBY

--EXEC('SELECT * FROM #RESULTS ' + @.ORDERBY)

SELECT * FROM #RESULTS WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

SET @.PAGES = Round(@.totalRows / @.PageSize,0,1) + CASE WHEN @.TotalRows % @.PageSize = 0 THEN 0 ELSE 1 END

-- Return Total number of pages and Total number of Rows

SELECT @.PAGES AS PageCount,

@.TOTALROWS AS TotalRecords

|||

Ok. I got your point.

Let me clarify here,

You will pass the number of records on each page or number of pages you required.

Once the input passed you have to identify the each row's page number.

If you use SQL Server 2005 its very simple, we have to change the previous query slightly.

With Assumption1: Passing Number of Record for each page..

Code Snippet

Create Proc FetchDataWithPaging_ForNoOfRowPerPage

(@.RowsPerPage int)

as

Begin

;With CTE

as

(

Select Name,Id,XType , Row_Number() Over(order By name) RowNumfrom Sysobjects

)

Select *, ((RowNum-1)/@.RowsPerPage) + 1 as Page from CTE

Order By Name

--Total Recodrs & pages

Select Count(*) as TotalRows,Count(*)/@.RowsPerPage as TotalPages From Sysobjects

End

go

Exec FetchDataWithPaging_ForNoOfRowPerPage 1000

With Assumption 2 -- Passing Required Pages,

Code Snippet

Create Proc FetchDataWithPaging_ForNoOfPage

(@.RequiredPages int)

as

Begin

Declare @.TotalRecords as Int;

Declare @.RowsPerPage as Int;

Select Name,Id,XType into #t from Sysobjects;

Select @.TotalRecords = Count(*) From #t

Select @.RowsPerPage = Round(Cast(@.TotalRecords as float)/Cast(@.RequiredPages as float),0)

;With CTE

as

(

Select * , Row_Number() Over(order By name) RowNumfrom #T

)

Select *, ((RowNum-1)/@.RowsPerPage) + 1 as Page from CTE

Order By Name

--Total Recodrs & pages

Select Count(*) as TotalRows,Count(*)/@.RowsPerPage as TotalPages, @.RowsPerPage RowsPerPage From #T

End

go

Exec FetchDataWithPaging_ForNoOfPage 10

|||

So last dumb question:

So would I take my entire SELECT query, including the JOIN's and stick it in that CTE piece?

Is there another way, because frankly I am utterly confused as to how I would convert this query:

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description

, AC.AccountProvider

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentId,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentId = PY.AccountPaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

--WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

WHERE ((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

ORDER BY AccountPaymentID

...into your query. Also, mine is already contained within a stored procedure.

I apologize for my paging ignorance. I'm not new to SQL Server, but I am new to the whole sorting thing via SQL Server so slowly picking it up. Is there any way to take what I already have and just tweak it a bit. The with CTE has me kind of baffled...

|||

Yes. You can do that. The query result will be act as simple table (only for writing query) on CTE.

|||

Last one, I swear...

I've altered my query to utilize a CTE as:

Code Snippet

BEGIN

DECLARE @.PageSize INT

SET @.PAgeSize = 1000

;

WITH CTE

AS

(

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description AS AccountDescription

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentID,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentID) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentID = PY.AccountPaymentID)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

)

Select *, ((RowNum-1)/@.PageSize) + 1 as Page from CTE

end

When I run the query, I'm getting the following error:

Msg 207, Level 16, State 1, Line 67

Invalid column name 'RowNum'.

Am I missing something?|||

You have to expose the Rownum in the SELECT clause of the toppermost query in the CTE:

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

....

, PE.clm_no

, CM.clmnt_idno

, SQ.RowNum

FROM ...

I think SQ is the right alias, but it is the right gist.