Determine if more than one row returned

[CJM]

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

 

[Bob Barrows [MVP]]

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

 

[CJM]

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

 

[Bob Barrows [MVP]]

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?

 

[Bob Barrows [MVP]]

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>

 

[CJM]

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

 

[Gert-Jan Strik]

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

 

[Luke Zhang [MSFT]]

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.)

 

[Bob Barrows [MVP]]

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.

 

[Bob Barrows [MVP]]

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.

 

[CJM]

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


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

 

[Alexander Kuznetsov]

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