SQL Order By in paging algorithim

Polymorpher

ok, here is the code: Private Sub CreateProcedure(ByVal con As SqlClient.SqlConnection) Dim curRowIndex As Integer = ((GetCurPageIndex() * RowsPerPage) + 1) If curRowIndex > GetTotalRowCount() Then mCurPageIndex = NumPagesAvailable() curRowIndex = GetTotalRowCount() End If Dim createProcedureSQL As String = "CREATE PROCEDURE [dbo].[PageResultsProcedure]" createProcedureSQL &= "AS" & vbCr createProcedureSQL &= "DECLARE @FirstID INT, @StartRow INT" & vbCr createProcedureSQL &= "SET ROWCOUNT " & curRowIndex & vbCr createProcedureSQL &= "SELECT @FirstID = " & TablePKName & " FROM " & TableName & " " & WhereSegment & vbCr createProcedureSQL &= "SET ROWCOUNT " & RowsPerPage & vbCr createProcedureSQL &= "SELECT " & ColumnsToReturn & " FROM " & TableName & CStr(IIf(WhereSegment = "", " WHERE ", " " & WhereSegment & " AND ")) & TablePKName & " >= @FirstID" & vbCr createProcedureSQL &= "ORDER BY " & TablePKName & vbCr createProcedureSQL &= "SET ROWCOUNT 0" Dim cmd As SqlClient.SqlCommand cmd = con.CreateCommand cmd.CommandText = createProcedureSQL con.Open() cmd.ExecuteNonQuery() con.Close() cmd.Dispose() cmd = Nothing End Sub This code is part of a procedure to allow paging a data table...the question is; I need a way of doing this, but being able to sort the results by a given column that is not guaranteed to have unique values in the table...Any Ideas? Thanks in advance for any help.

-- "Keyboard not found. Press < F1 > to RESUME. " Source unknown (appears in many common BIOSes as a real error message)

Colin Angus Mackay

Umm... Wow! There are a number of things that are wrong with this before we even get to solving the problem you posted about. 1. Why are you creating a stored procedure like this? 2. Your code is vulnerable to a SQL Injection attack. 3. The use of SET ROWCOUNT should be discouraged because it will affect other queries that happen to be running at the same time. Use SELECT TOP n instead (where n is the number of rows you want) If you are running SQL Server 2005 the answer is quite simple as there is some new functionality built in to address this problem. If you are running SQL Server 2000 then it is workable, but the query will be more complex. Which version of SQL Server are you using?

Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website

Colin Angus Mackay

Just suddenly remembers that I'd seen an article here on Code Project that discusses what you want to do (assuming you are using SQL Server 2005). http://www.codeproject.com/cs/database/PagingResults.asp[^]

Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website

Polymorpher

I'm using SQL Server 2000, and the code wont actually be building the query in the long run, this is just basically a proof of concept and is like this for the time being so that I can play with it in a designer I coded...SET ROWCOUNT is set back to 0 at the end to restore it for latter query's. An explanation of why I'm using SET ROWCOUNT can be found here: http://www.4guysfromrolla.com/webtech/042606-1.shtml[^] It is a fairly efficient way of doing this... I'm only trying to find a way to incorporate an order by into this...

-- "Keyboard not found. Press < F1 > to RESUME. " Source unknown (appears in many common BIOSes as a real error message)

Polymorpher

http://www.codeproject.com/script/comments/forums.asp?forumid=1725&select=2209685&df=100&msg=2209685[^]

-- "Keyboard not found. Press < F1 > to RESUME. " Source unknown (appears in many common BIOSes as a real error message)

Colin Angus Mackay

Polymorpher wrote:

SET ROWCOUNT is set back to 0 at the end to restore it for latter query's. An explanation of why I'm using SET ROWCOUNT can be found here: http://www.4guysfromrolla.com/webtech/042606-1.shtml\[^\] It is a fairly efficient way of doing this...

Maybe, but it is also a fairly dangerous thing to do. SET ROWCOUNT will affect other queries running at the same time. If you are never going to have other users then this is fine. But in that case why are you using something like SQL Server. As for incorportating an order. Use subquerys - If you expect to be paging through lots of data then you may wish to use a temp table for the innermost SELECT. Also, remember to only use the columns you actually need, this will help the performance of the query rather than the demonstration SELECT * I've used here. First lets, say you want rows 76 to 100 (25 rows in total). Get the first 100 rows

SELECT TOP 100 *
FROM MyTable
ORDER BY OrderColumn

Then Reverse the order of the result set getting only 25 rows

SELECT TOP 25 *
FROM (SELECT TOP 100 *
FROM MyTable
ORDER BY OrderColumn)
ORDER BY OrderColumn DESC

Finally, flip the order back again:

SELECT *
FROM (SELECT TOP 25 *
FROM (SELECT TOP 100 *
FROM MyTable
ORDER BY OrderColumn)
ORDER BY OrderColumn DESC)
ORDER BY OrderColumn

Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website

Colin Angus Mackay

Given that you didn't say which version of SQL Server in your original post...

Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website