RowNum with SQLServer

I'm working IIS 4, and the problem is that I don't want to get the whole resoult set but 50 rows each time.

Note that the where clause of the select query is dynamic , meens that I can't use a fixed identity column.
I can run a stored procedure with the where clause that will update my identity column but this will not help because their are many users and each one of them can make a defrent where caluse.

Why don't you post your table's structure?
Let me help you.
PD:A sample data would be useful.

You can create a stored procedure that retrieves n rows from your table taking into account a specific column value. Check the code and let me know your opinion.

create table test_table (col_id int,col_data char(1))

declare @liId int
select @liId=1
while @liId<201
  begin
    insert test_table
    select @liId,(select char(cast(rand()*25 as int)+65))
    select @liId=@liId+1
  end

create proc spRetrieveData
 @liRowsReturned int=1 output -- specifies how many rows are returned
,@liLastRowId int=0 output -- returns last row id of the record set
as
begin
  select @liLastRowId=isnull(@liLastRowId,0)
        ,@liRowsReturned=isnull(@liRowsReturned,1)
  set rowcount @liRowsReturned
  select *
    from test_table
   where col_id>@liLastRowId

  select @liLastRowId=col_id
    from test_table
   where col_id>@liLastRowId

  select @liRowsReturned=@@rowcount
  set rowcount 0
end


declare
 @liRowsReturned int
,@liLastRowId int

select
 @liRowsReturned=50
,@liLastRowId=49

exec spRetrieveData
     @liRowsReturned output
    ,@liLastRowId output

select
 @liRowsReturned
,@liLastRowId

This whole code is to havy.
I can't run a stored procedure before any select taht a user does.

Change string (5) from my example as you like.

CREATE PROCEDURE sp_fetch (@begin_id int, @end_id int) AS
declare @row_count int

set nocount on
select * into #tb_temp from pubs.dbo.authors --(5)
declare fetch_cursor cursor  for select * from #tb_temp
set @row_count = 1

open fetch_cursor
FETCH NEXT FROM fetch_cursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
     if (@row_count < @begin_id) or (@row_count > @end_id)
        delete from #tb_temp WHERE CURRENT OF fetch_cursor
     set @row_count = @row_count + 1
     FETCH NEXT FROM fetch_cursor
END

select * from #tb_temp
close fetch_cursor
deallocate fetch_cursor


If you want to choose rows from 5 to 10 from table authors database pubs you may run the following:
exec sp_fetch 5, 10

Adjusted points to 300

Please, be more specific.

CREATE PROCEDURE sp_fetch (@text_query varchar (1000), @begin_id int, @end_id int) AS
declare @execstr varchar (1100)
declare @row_count int
declare @exmplestr varchar (200)

set nocount on
set @exmplestr = REPLACE(CONVERT(varchar(80), newid()), '-', '_')
set @execstr = STUFF(@text_query, PATINDEX('%from%', @text_query) , 0, ' into tempdb..a' + @exmplestr + ' ')
exec (@execstr)

set @execstr = 'declare fetch_cursor cursor  for select * from tempdb..a' + @exmplestr
exec (@execstr)
set @row_count = 1

open fetch_cursor
FETCH NEXT FROM fetch_cursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
     if (@row_count <@begin_id) or (@row_count > @end_id)
        BEGIN
            set @execstr = 'delete from tempdb..a' + @exmplestr + ' WHERE CURRENT OF fetch_cursor '
            exec (@execstr)
        END
     set @row_count = @row_count + 1
     FETCH NEXT FROM fetch_cursor
END

set @execstr = 'select * from tempdb..a' + @exmplestr
exec (@execstr)
close fetch_cursor
deallocate fetch_cursor


It's my test example:

exec sp_fetch
'use pubs
SELECT t1.au_lname
FROM authors t1 INNER JOIN titleauthor t2
ON t1.au_id = t2.au_id', 5, 10

You can transfer any queries as first parameter, second parameter - first record for selection, third parameter - last record for selection.

I know, that it will work slowly and SQL Server is not intended for such navigation, but...
By the way, read about "Does SQL Server support row numbers like Oracle does?":
http://www.ntfaq.com/ntfaq/sql183.html#sql183

P.S. It's example for MS SQL Server 7.0.

This solution is really very bad for me because it may be very havy to run this stored procedure instead of any select query I need to do.

This application is going to run on a web page with IIS. I can't run a stored procedure for evry select on the screen.

If you are using IIS 4.0 then why not use server side scripts to obtain a recordset and display only the number of records that you want. Then if the use updates you can display more records continuing from where you left off. I use this sucessfully with MSSQL7 and IIS 4.0 for web pages that may have to display thousands of records. Using this approach puts the load on the IIS server rather than the client .

mmips, can u please be more specific?
How can I open a recordset in the IIS side and not close it when the page is being send ti the client and then use it again when the client want more records?

I assume that you are working with IIS 4.0, you probably code with ASP

in ASP, we can display record 1-20 in first page, and 21-40 in second page, and so on.

it goes like this:

<%
myConn = "DSN=myDSN;UID=SA;PWD=;"

myPage= Request("WhichPage")
if myPage="" then
   myPage = 1
End if
mypagesize=request("pagesize")
If  mypagesize="" then
   mypagesize=10
end if
mySQL=request("SQLquery")
IF  mySQL="" THEN
   mySQL=SQLtemp
END IF

set rstemp=Server.CreateObject("ADODB.Recordset")
rstemp.cursorlocation=aduseclient
rstemp.cachesize=5
rstemp.open mySQL,connectme
rstemp.movefirst
rstemp.pagesize=mypagesize
maxpages=cint(rstemp.pagecount)
maxrecs=cint(rstemp.pagesize)
rstemp.absolutepage=mypage
howmanyrecs=0
howmanyfields=rstemp.fields.count -1
response.write "Page " & mypage & " of " & maxpages & "<br>"
response.write "<table border='1'><tr>"

'Put Headings On The Table of Field Names
FOR i=0 to howmanyfields
   response.write "<td><b>" & rstemp(i).name & "</b></td>"
NEXT
response.write "</tr>"

' Now loop through the data
DO  UNTIL rstemp.eof OR howmanyrecs>=maxrecs
   response.write "<tr>"
   FOR i = 0 to howmanyfields
      fieldvalue=rstemp(i)
      If isnull(fieldvalue) THEN
         fieldvalue="n/a"
      END IF
      If trim(fieldvalue)="" THEN
         fieldvalue="&nbsp;"
      END IF
      response.write "<td valign='top'>"
      response.write fieldvalue
      response.write "</td>"
   next
   response.write "</tr>"
   rstemp.movenext
   howmanyrecs=howmanyrecs+1
LOOP
response.write "</table><p>"

' close, destroy
rstemp.close
set rstemp=nothing

' Now make the page _ of _ hyperlinks
Call PageNavBar

sub PageNavBar()
      pad=""
      scriptname=request.servervariables("script_name")
      response.write "<table rows='1' cols='1' width='97%'><tr>"
      response.write "<td>"
      response.write "<font size='2' color='black' face='Verdana, Arial,Helvetica, sans-serif'>"
      if (mypage mod 10) = 0 then
       counterstart = mypage - 9
      else
       counterstart = mypage - (mypage mod 10) + 1
      end if
      counterend = counterstart + 9
      if counterend > maxpages then counterend = maxpages
      if counterstart <> 1 then
       ref="<a href='" & scriptname
       ref=ref & "?whichpage=" & 1
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>First</a>&nbsp;:&nbsp;"
       Response.Write ref


       ref="<a href='" & scriptname
       ref=ref & "?whichpage=" & (counterstart - 1)
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>Previous</a>&nbsp;"
       Response.Write ref
      end if
      Response.Write "["
      for counter=counterstart to counterend
       If counter>=10 then
       pad=""
       end if
       if cstr(counter) <> mypage then
       ref="<a href='" & scriptname
       ref=ref & "?whichpage=" & counter
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>" & pad & counter & "</a>"
       else
       ref="<b>" & pad & counter & "</b>"
       end if
       response.write ref
       if counter <> counterend then response.write " "
      next
      Response.Write "]"
      if counterend <> maxpages then
       ref="&nbsp;<a href='" & scriptname
       ref=ref & "?whichpage=" & (counterend + 1)
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>Next</a>"
       Response.Write ref


       ref="&nbsp;:&nbsp;<a href='" & scriptname
       ref=ref & "?whichpage=" & maxpages
       ref=ref & "&pagesize=" & mypagesize
       ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
       ref=ref & "'>Last</a>"
       Response.Write ref
      end if
      response.write "<br></font>"
      response.write "</td>"
      response.write "</table>"
end sub
%>



I hope it help, please let me know information above code

I am also interested in the output of the discussion

If you are using Interdev the DTC for recordset allows setting the number of records per page. If not then The server side type of script is a function that returns the number of records you displayed the first page second call passes that number back reopens a recordset and starts from there as an absolute position.This provides a decent way of displaying large numbers of records. I prefer the data grid DTC.

In order to implement a rownum feature the following solutions would help out.

1.If your table already consists an identity column you may use it equivalent to rownum.

2.The following method would help you out in any situation.

Create a temporary table with an identity column and based on that you retrieve the rows. This would be helpful even if a table does'nt contains an identity column.

hdrink, please answer this question, so I would be able to give you the points