batch selects by rownum? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

batch selects by rownum?

Author

Topic

mrzope
Starting Member

7 Posts

Posted - 2002-08-28 : 19:02:07

I have a large set of data that I want to step through much like this forum does by showing only x amount of topics per page. My primary keys can't be used for this cause if something gets deleted... I have came up with a solution but it is sssllooowwww. To the point of "I don't want to do it that way at all".

So my question is, how do you do it then?????

Here is the way I did it:
First I made a view:
SELECT TOP 100 PERCENT d.item_num,
(SELECT COUNT(item_num)
FROM dbo.item_data d2
WHERE d2.item_num <= d.item_num) AS rownumber, d.item_desc_short, d.item_inv, dbo.item_price_rel.price,
dbo.item_manus.item_manu_name
FROM dbo.item_data d INNER JOIN
dbo.item_manus ON d.rel_item_manu = dbo.item_manus.manu_ID INNER JOIN
dbo.item_price_rel ON d.item_num = dbo.item_price_rel.rel_item_num
ORDER BY d.item_num

Then... I have a stored proc that selects the range from the view:
SELECT item_num, price AS item_price, item_inv, item_desc_short, item_manu_name AS item_manu
FROM dbo.item_rows_view
WHERE (rownumber BETWEEN @start AND @end)

There has got to be a faster way!!! Thanks

I ate what?

mrzope
Starting Member

7 Posts

Posted - 2002-08-29 : 02:22:58

Wow, I have seen many questions about this but no real answers. The only thing I can think of is create a column called rownum and create an insert update delete trigger that re-calculates and populates the rownum each execution. Then do a simple WHERE rownum BETWEEN 1 and 50. Which may be ok for a small to medium amount of records. Like under 10,000. Any other suggesions?

I ate what?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-29 : 05:20:58

I think there is an article on this.
I think Joe Celko wrote something some time ago somewhere too.
(Haven't read either of them embarrassed to say).

Couple of comments
Do you need to order the view? The select is done on rownum.

In your other post the rownum should probably be on another table linking to the promary key of the main table to reduce the number of page accesses when reallocating.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-08-29 : 08:25:25

Let me get this straight: All you need is to split a table into pages?

Try the code below. I know it's dirty, and you may have to alter it a bit to suit your needs, but it's reasonably fast.

Here goes:

set nocount on

declare @count int, @page int, @rowsperpage int
declare @table varchar(50), @orderbycolumn varchar(50)
drop table #tmp
create table #tmp (c int)

select @table = 'tcst' --The name of the table
select @orderbycolumn = 'cstid' --Column to order by
select @page=9 --page to retrieve (should not be higher than @count/@rowsperpage)
select @rowsperpage=50 --Rows per page

declare @sql nvarchar(4000)
select @sql = 'select count(*) from ' + @table + ' (nolock)'

insert #tmp
exec sp_executesql @sql

select @count=c from #tmp

select @sql= 'select top ' + convert(varchar, @rowsperpage) + ' * '
select @sql = @sql + 'from (select top ' + convert(varchar, @count-((@page-1)*@rowsperpage))
select @sql = @sql + ' * from ' + @table + ' (nolock) order by ' + @orderbycolumn + ' desc) as der'
select @sql = @sql + ' order by ' + @orderbycolumn

exec (@sql)

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-29 : 08:43:27

select @sql = 'select count(*) from ' + @table + ' (nolock)'
insert #tmp
exec sp_executesql @sql
select @count=c from #tmp

same as

select @count = rows from sysindexes where id = object_id(@table) and indid in (1,2)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-08-29 : 09:11:10

I stand corrected :)

mrzope
Starting Member

7 Posts

Posted - 2002-08-29 : 17:16:37

OK I am trying out the temp table sql from nr...

ALTER PROCEDURE dbo.records
@sql nvarchar(100), @table char(50), @count int
AS
CREATE TABLE #tmp(c int)

SELECT @sql = 'SELECT count(*) FROM ' + @table + ' (nolock)'
INSERT #tmp
EXEC sp_executesql @sql
SELECT @count=c from #tmp

@table = my_table, @count = the row I want returned???

When this executes, It doesn't give an error it just doesn't return any results. What am I doing wrong?

I ate what?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-29 : 19:59:18

Is this the article you were looking for?
What's After Top?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-08-30 : 03:33:39

mrzope, as I understand it you are executing this procedure:

ALTER PROCEDURE dbo.records
@sql nvarchar(100), @table char(50), @count int
AS
CREATE TABLE #tmp(c int)

SELECT @sql = 'SELECT count(*) FROM ' + @table + ' (nolock)'
INSERT #tmp
EXEC sp_executesql @sql
SELECT @count=c from #tmp

That code is taken out of it's context and will not return any data. It will only assign a value to the @count variable and then quit. Please look at the article from AjarnMark or use the whole of my code above.

Regards,
Kalle Dahlberg

mrzope
Starting Member

7 Posts

Posted - 2002-08-30 : 20:24:39

quote:

mrzope, as I understand it you are executing this procedure:

ALTER PROCEDURE dbo.records
@sql nvarchar(100), @table char(50), @count int
AS
CREATE TABLE #tmp(c int)

SELECT @sql = 'SELECT count(*) FROM ' + @table + ' (nolock)'
INSERT #tmp
EXEC sp_executesql @sql
SELECT @count=c from #tmp

That code is taken out of it's context and will not return any data. It will only assign a value to the @count variable and then quit. Please look at the article from AjarnMark or use the whole of my code above.

Regards,
Kalle Dahlberg

Sorry, I got a bit confused.

I have change the @table and @orderbycolumn to my particular db and it is not returning any results.

I ate what?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-09-04 : 18:01:07

mrzope, didn't the What's After Top? article help? Aren't you just talking about paging through a recordset?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources