Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Posts
    2

    Unanswered: using table variable in order to limit recordset

    Hi,
    I have written a SP that limits the number of recordset that is returned. It works in a way that:
    it takes from what number to start and how many record will be returned. I mean I want to get the records starting from 60 until 70. I will use this SP in paging the asp pages. In order to do this, I used a Cursor, that counts until 60 without doing anything, then cursor sends fields into a table that is created in RAM(not written in File System).
    When it is in the 70 record, loop ends end, I get the selected records using
    SELECT * FROM @TableInRam
    It works fine in SQL Server, but when I used ADO in an asp page, I get the error that says that Recordset object is closed.

    Do you know why it works in SQL Server but not in ASP?

    Or do you have any suggestion else than the following?

    Note: I don't want to use a temp db in file system. And TOP does not work as it does not accept variable.

    There may be one solution that inserting selected records into a two dimensional array and returning it to the ASP. But I don't know whether we can use arrays in SQL statements.

    Thanks for your answers and help!

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Can't you do:

    Code:
    SET ROWCOUNT @count
    
    SELECT *
      FROM table
     ORDER BY something
    Thanks,

    Matt

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Could you be more specific on what problem you are trying to solve ? Are you trying to limit the amount of data displayed on your asp pages ? - for example, the user is requesting 100 records from your database - but you only want to display 10 records on each page. Have you programmed ado with stored procedures (where you are creating a recordset from the results) before or is this the first time ?

  4. #4
    Join Date
    Sep 2002
    Posts
    2
    Exactly as you said.
    I am using a stored procedure and I want to get the recordset starting from some number upto some.

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    hackemall, are you aware that when you declare a variable of type TABLE that the data is stored in TEMPDB NOT RAM. Try this:

    Code:
    select * from tempdb..sysobjects where type = 'U'
    go
    declare @Tbl table (f1 int, f2 varchar(1))
    select * from tempdb..sysobjects where type = 'U'
    go
    select * from tempdb..sysobjects where type = 'U'
    go
    As for your problem, if the data does not change often I would suggest getting as many rows back from your stored procedure as you can resonably hanbdle in ASP and use ASP to display a window of data.
    If your data is constantly changing then I would create a temp table on the DBServer with an identiy column, run your select statment inserting data into the temp table and then select rows from the temp table based on the identity column. IMHO, I feel this would be faster, simpler and more efficient than a cursor.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Mar 2003
    Posts
    144
    You may try creating an stored procedures thats creates another that has a SELECT TOP statement. Then, you could execute the new stored procedure and after that, drop it:

    create procedure P1
    @NumRecords numeric
    as

    declare @SQL varchar(1000)

    set @SQL='
    create procedure P2

    as

    select top ' + ltrim(str(@NumRecords)) + ' from Table1'

    execute (@SQL)
    execute P2
    execute ('drop procedure P2')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •