Paging a query

I am trying to page my data and am using ROW_NUMBER() to accomplish this.

I want to be able to pass the starting and ending row numbers, as well as 
the order by column.

I can change the following query with parameters.
**************************************
With PagedResults AS
(
 SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
OVER (ORDER BY LastName, FirstName) AS ResultSetRowNumber
 FROM HumanResources.vEmployee
)
SELECT *
FROM PagedResults
WHERE ResultSetRowNumber > 40 AND ResultSetRowNumber <= 50
**************************************************

Where the Order By and row number would be dynamic.

Would I have to set this up as a dynamic SQL to accomplish this?

Or could I do something like:
*********************************************
With PagedResults AS
(
 SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
OVER (ORDER BY @SortColumns) AS ResultSetRowNumber
 FROM HumanResources.vEmployee
)
SELECT *
FROM PagedResults
WHERE ResultSetRowNumber > @StartRow AND ResultSetRowNumber <= @EndRow
***************************************************

I know I can use the parameters for the WHERE clause but I can't seem to for 
the ORDER BY clause.

Would I have to use dynamic sql for this?

Thanks,

Tom



0
tshad
2/4/2010 6:16:52 AM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
1137 Views

Similar Articles

[PageSpeed] 14

Hi
Perpaps CASE Expression

ORDER BY CASE @Direction
           WHEN 'DESC'
           THEN
             CASE @OrderBy
               WHEN 'FirstName' THEN firstname
               WHEN 'TelNo' THEN TelNo
               WHEN 'Email' THEN email
               WHEN 'FaxNo' THEN FaxNo
             END



"tshad" <t@dslextreme.com> wrote in message 
news:ekW%23lGWpKHA.3776@TK2MSFTNGP04.phx.gbl...
>I am trying to page my data and am using ROW_NUMBER() to accomplish this.
>
> I want to be able to pass the starting and ending row numbers, as well as 
> the order by column.
>
> I can change the following query with parameters.
> **************************************
> With PagedResults AS
> (
> SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
> OVER (ORDER BY LastName, FirstName) AS ResultSetRowNumber
> FROM HumanResources.vEmployee
> )
> SELECT *
> FROM PagedResults
> WHERE ResultSetRowNumber > 40 AND ResultSetRowNumber <= 50
> **************************************************
>
> Where the Order By and row number would be dynamic.
>
> Would I have to set this up as a dynamic SQL to accomplish this?
>
> Or could I do something like:
> *********************************************
> With PagedResults AS
> (
> SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
> OVER (ORDER BY @SortColumns) AS ResultSetRowNumber
> FROM HumanResources.vEmployee
> )
> SELECT *
> FROM PagedResults
> WHERE ResultSetRowNumber > @StartRow AND ResultSetRowNumber <= @EndRow
> ***************************************************
>
> I know I can use the parameters for the WHERE clause but I can't seem to 
> for the ORDER BY clause.
>
> Would I have to use dynamic sql for this?
>
> Thanks,
>
> Tom
>
>
> 


0
Uri
2/4/2010 8:55:55 AM
On Wed, 3 Feb 2010 22:16:52 -0800, tshad wrote:

>Would I have to use dynamic sql for this?

Yes. Unless the amount of possible orders is limited, in that case you
can use IF ... ELSE IF ... ELSE

-- 
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
0
Hugo
2/4/2010 9:00:27 AM
You cannot use a variable to define the sort order in ROW_NUMBER. The syntax allows the variable use but it treats the 
variable as constant expression and you do not get sort order by the value. As Uri/Hugo suggested if you have limited 
combination of sort columns you can use a CASE expression/IF..ELSE or you would have to use dynamic SQL.

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
2/4/2010 2:57:50 PM
Kind of what I figured.

Thanks,

Tom

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:ctadnaQZ9InxfffWnZ2dnUVZ_tSsnZ2d@speakeasy.net...
> You cannot use a variable to define the sort order in ROW_NUMBER. The 
> syntax allows the variable use but it treats the variable as constant 
> expression and you do not get sort order by the value. As Uri/Hugo 
> suggested if you have limited combination of sort columns you can use a 
> CASE expression/IF..ELSE or you would have to use dynamic SQL.
>
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com 


0
tshad
2/4/2010 7:33:15 PM
Reply:

Similar Artilces: