Get rows using SP

[faccorp]

I've seen some sample code on how to get a page of data from a large table using a stored procedure.

However, I need to be able to get a range of rows rather than a page. This sounds like the same thing, or maybe it is, however, the range of rows may or may not be based on the identity column. For example, if the client want the data sorted by last name, and it asks for rows 1 through 20, then identity won't work. I want the SP to be able to handle any table with the ability to know what column we are sorting by and what range of rows to return (based on the current sort). Does this make sense?

Any feedback would be appreciated.


The backgroung is - my client app is VC++ using OLE DB. I wrote what I thought was a good interface from the client to access the rows I wanted and display them in a virtual list control. I have been told that it is best to do as much programming in a Stored Procedure rather than on the client. So I am now in the redesign mode.

Thanks

 

[faccorp]

I've been reading through this forum and I've seen many mentions to cursors and that they are a bad thing and should be avoided. However, in reading through Books Online, it seems that cursors might get me want I want here.

"For these applications, SQL Server offers server cursors that allow an application to fetch a small subset or block of rows from an arbitrarily large result set. If the user wants to see other records from the same result set, a server cursor allows the application to fetch any other block of rows from the result set, including the next n rows, the previous n rows, or n rows starting at a certain row number..."

I am mostly interested in getting n rows starting at a certain row number.


Is this the best way to go here??

Thanks

 

[Pattycake245]

Try this thread, there is usually always a way to get the results without using cursors.

thread183-895525

Tim

 

[faccorp]

Ok,

How about this as my stored procedure:

CREATE PROCEDURE [dbo].[GetPage] 

(
	@TableName char(20),
	@start INTEGER,
	@cnt INTEGER,
	@orderby char(20)
)

AS

DECLARE @cnt_s char(20)
DECLARE @start_s char(20)
SET @cnt_s = STR(@cnt)      /* create a string value */
SET @start_s = STR(@start)    /* create a string value */


/*  Build the query string */

EXEC ('SELECT TOP ' + @cnt_s + ' * FROM ' + @TableName + ' WHERE idnumber NOT IN (SELECT TOP ' + @start_s + ' idnumber FROM ' + @TableName + ' ORDER BY ' + @orderby + ') ORDER BY ' + @orderby)


GO

So I can pass in the table name, the first row I want, and the total number of rows, plus the order that I want the table sorted by.


Then in my client, I call the SP whenever I show data in my virtual list view control.

Will performance be an issue here?


Thanks

 

[hmckillop]

So your application has the option of sorting on different fields and thus paging on different criteria.
If I was doing this I would create seperate stored procedures based on each sort column , i.e.
usp_GetPageOfMYDataByCol1, usp_GetPageOfMYDataByCol2 etc.
You would have to know at the app layer what column you are sorting by, so at that stage you can change which procedure is called.
Then within the stored procedures you use TOP X, with X being your page size and explicitly order by whatever column you require. Also note that the chances of each column being unique (apart from identity) is slim, so you need to be careful when paging by these items. i.e. If you have a page size of 20 and you are ordering by lastname column, but you currently have 25 Smiths in your DB table. How do you know where page 2 begins if you are using this column to determine start of each page number.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

In answer to the performance question, your procedure shown above will always mean a scan of the tables queried - as you use not in, the DB then has to scan the whole table to eradicate these rows.

Performance will be an issue if you have large tables due to scanning and also the proc will recompile each time its executed.


"I'm living so far beyond my income that we may almost be said to be living apart