Determine if more than one row returned

Discussion in 'ASP General' started by CJM, Jun 28, 2006.

  1. CJM

    CJM Guest

    I have an ASP/ADO application querying an SQL Server DB. I want know the
    most efficient way to determine if more than one row is returned from a
    query. If more than one row is returned, the user will be presented with a
    choice of which row to process. If only one row is returned, I want to skip
    this stage, and process that single row immediately.

    I can think of a number of ways of acheiving this (eg. .recordcount) but I'm
    looking for the slickest and most efficient method.

    Any thoughts or suggestions

    Thanks

    CJM
     
    CJM, Jun 28, 2006
    #1
    1. Advertising

  2. CJM wrote:
    > I have an ASP/ADO application querying an SQL Server DB. I want know
    > the most efficient way to determine if more than one row is returned
    > from a query. If more than one row is returned, the user will be
    > presented with a choice of which row to process. If only one row is
    > returned, I want to skip this stage, and process that single row
    > immediately.
    >
    > I can think of a number of ways of acheiving this (eg. .recordcount)
    > but I'm looking for the slickest and most efficient method.
    >
    > Any thoughts or suggestions
    >

    Best option: Use a stored procedure that only returns the data if more
    than one row meeting the criteria exist.

    Second best:
    Use GetRows to put the recordset data into an array and check its upper
    index bound using the ubound function.
    This has the added benefit of allowing you to:
    1. use the efficient server-side forward-only cursor (which does not
    support recordcount)
    2. immediately close and destroy the recordset



    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jun 28, 2006
    #2
    1. Advertising

  3. CJM

    CJM Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:O$...
    >>

    > Best option: Use a stored procedure that only returns the data if more
    > than one row meeting the criteria exist.
    >
    > Second best:
    > Use GetRows to put the recordset data into an array and check its upper
    > index bound using the ubound function.
    > This has the added benefit of allowing you to:
    > 1. use the efficient server-side forward-only cursor (which does not
    > support recordcount)
    > 2. immediately close and destroy the recordset
    >
    >

    Bob,

    Thanks for the response...

    Option 2 is the best in this case... there may only be one row returned
    (which is fine) but in this case I need to handle it differently...

    Thanks

    Chris
     
    CJM, Jun 29, 2006
    #3
  4. CJM wrote:
    > "Bob Barrows [MVP]" <> wrote in message
    > news:O$...
    >>>

    >> Best option: Use a stored procedure that only returns the data if
    >> more than one row meeting the criteria exist.
    >>
    >> Second best:
    >> Use GetRows to put the recordset data into an array and check its
    >> upper index bound using the ubound function.
    >> This has the added benefit of allowing you to:
    >> 1. use the efficient server-side forward-only cursor (which does not
    >> support recordcount)
    >> 2. immediately close and destroy the recordset
    >>
    >>

    > Bob,
    >
    > Thanks for the response...
    >
    > Option 2 is the best in this case... there may only be one row
    > returned (which is fine) but in this case I need to handle it
    > differently...
    >

    Why not handle it in the stored procedure?
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jun 29, 2006
    #4
  5. CJM wrote:
    > "Bob Barrows [MVP]" <> wrote in message
    > news:O$...
    >>>

    >> Best option: Use a stored procedure that only returns the data if
    >> more than one row meeting the criteria exist.

    >
    > Thanks for the response...
    >
    > Option 2 is the best in this case... there may only be one row
    > returned (which is fine) but in this case I need to handle it
    > differently...
    >

    Just to expand, something like this:

    create procedure ...
    declare @rows int
    set @rows=(select count(*) from table where <criteria>)
    if @rows=0
    do something
    return
    if @rows = 1
    do something else
    return
    if @rows > 1
    select <columns> from table where <criteria>



    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jun 29, 2006
    #5
  6. CJM

    CJM Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    >>

    > Just to expand, something like this:
    >
    > create procedure ...
    > declare @rows int
    > set @rows=(select count(*) from table where <criteria>)
    > if @rows=0
    > do something
    > return
    > if @rows = 1
    > do something else
    > return
    > if @rows > 1
    > select <columns> from table where <criteria>
    >


    It's client-side stuff...need user interaction...

    If no rows returned, display error message
    If 1 row returned, immediately go to editing page for that row...
    If several rows returned, display list. User picks row and it redirects to
    editing page...

    CJM
     
    CJM, Jun 29, 2006
    #6
  7. Hi CJM,

    If there are at most 2 values, then most methods will probably be
    equally fast. However, if there are cases where there are much more than
    2 values, then the solution below is probably the fastest. This is
    because query execution will end when the second value is found.

    Note however, that it is based on the proprietary behavior of SQL Server
    that does not require just one row/value to be assigned to a local
    variable.

    Declare @the_value int

    SELECT TOP 2 @the_value = value
    FROM MyTable
    WHERE some_column = 'some value'

    If @@rowcount > 1
    Begin
    print 'Please select a value'
    ...
    End
    Else
    SELECT @the_value AS "This is the value"


    HTH,
    Gert-Jan


    CJM wrote:
    >
    > I have an ASP/ADO application querying an SQL Server DB. I want know the
    > most efficient way to determine if more than one row is returned from a
    > query. If more than one row is returned, the user will be presented with a
    > choice of which row to process. If only one row is returned, I want to skip
    > this stage, and process that single row immediately.
    >
    > I can think of a number of ways of acheiving this (eg. .recordcount) but I'm
    > looking for the slickest and most efficient method.
    >
    > Any thoughts or suggestions
    >
    > Thanks
    >
    > CJM
     
    Gert-Jan Strik, Jun 29, 2006
    #7
  8. Hello,
    You can still use GetRows as Bob suggested:

    Use GetRows to put the recordset data into an array and check its upper
    index bound using the ubound function.

    In your ASP server side code, get the upper index bound and it is actually
    record number. Then, you can:

    1. Write an error message to user
    2. Redirect a User to page with single records.
    3. Display mutlple records for user choose.

    Regards,

    Luke Zhang
    Microsoft Online Community Lead

    ==================================================
    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.
    ==================================================

    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)
     
    Luke Zhang [MSFT], Jun 30, 2006
    #8
  9. Monkey Pi wrote:
    > Luke Zhang [MSFT] wrote:
    >> Hello,
    >> You can still use GetRows as Bob suggested:
    >>
    >> Use GetRows to put the recordset data into an array and check its
    >> upper index bound using the ubound function.


    >>

    >
    > I would use an output variable in your stored procedure. You could
    > set it to tell you the number of rows and have Select Case or IF
    > statements in your ASP that base on the value of the output variable.


    In this case, that's rather pointless isn't it? The procedure is going to be
    returning a resultset regardless of the value of the output variable.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jun 30, 2006
    #9
  10. Monkey Pi wrote:
    > Bob Barrows [MVP] wrote:
    >> Monkey Pi wrote:
    >>
    >>> Luke Zhang [MSFT] wrote:
    >>>
    >>>> Hello,
    >>>> You can still use GetRows as Bob suggested:
    >>>>
    >>>> Use GetRows to put the recordset data into an array and check its
    >>>> upper index bound using the ubound function.

    >>
    >>
    >>> I would use an output variable in your stored procedure. You could
    >>> set it to tell you the number of rows and have Select Case or IF
    >>> statements in your ASP that base on the value of the output
    >>> variable.

    >>
    >>
    >> In this case, that's rather pointless isn't it? The procedure is
    >> going to be returning a resultset regardless of the value of the
    >> output variable.

    >
    > I think that depends on whether there's more he wants do with GetRows
    > besides get a count.


    There is. Didn't you read his reply in this thread?

    > Setting up the output variable in the stored
    > procedure would take less than a minute. My first experience with
    > GetRows took considerably longer than that. Apologies if I'm
    > completely wrong on this.


    He needs records, regardless of how many tere are.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jun 30, 2006
    #10
  11. CJM

    CJM Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    >>>
    >>>
    >>>> I would use an output variable in your stored procedure. You could
    >>>> set it to tell you the number of rows and have Select Case or IF
    >>>> statements in your ASP that base on the value of the output
    >>>> variable.
    >>>
    >>>
    >>> In this case, that's rather pointless isn't it? The procedure is
    >>> going to be returning a resultset regardless of the value of the
    >>> output variable.

    >>
    >> I think that depends on whether there's more he wants do with GetRows
    >> besides get a count.

    >
    > There is. Didn't you read his reply in this thread?
    >
    >> Setting up the output variable in the stored
    >> procedure would take less than a minute. My first experience with
    >> GetRows took considerably longer than that. Apologies if I'm
    >> completely wrong on this.

    >
    > He needs records, regardless of how many there are.
    >


    Bob is right. As usual.

    Just to make the whole thread even more redundant, I've actually had my hand
    forced such that this is no longer needed (different UI and method of
    selection). However, I hope the thread will be useful to someone in a
    similar position.

    Thanks all

    CJM
     
    CJM, Jul 3, 2006
    #11
  12. If you are on SQL Server 2005, in some cases you can use row_number()
    as follows:

    select col1, col2, row_number() over(order by col1 desc) n
    from your_table
    order by col1

    col1 col2 n
    a bla 3
    b bl 2
    c cc 1
     
    Alexander Kuznetsov, Jul 12, 2006
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John Dalberg
    Replies:
    0
    Views:
    406
    John Dalberg
    Dec 20, 2005
  2. Charleees

    DELETE MORE THAN ONE ROW OF DATAGRID

    Charleees, Jun 16, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    519
  3. Merciadri Luca
    Replies:
    4
    Views:
    852
  4. Steven D'Aprano
    Replies:
    0
    Views:
    146
    Steven D'Aprano
    Dec 23, 2013
  5. Replies:
    3
    Views:
    119
    Gary Herron
    Dec 23, 2013
Loading...

Share This Page