Determining Security: Determine if more than one row returned

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
CJMCJM 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.|||Hi,
Thanks for your post!
My understanding of your issue is:
You wanted the most efficient way to retrieve the result row count.
If I have misunderstood, please let me know.
I recommend you write a common stored procedure retrieving the row count of
any query result.
Here is a sample just for reference:
create procedure proc_getqueryrowscount
(
@.strquery varchar(1000)
)
as
declare @.strcountquery varchar(1000)
select @.strcountquery = 'select count(*) from ( ' + @.strquery + ' ) v '
exec ( @.strcountquery )
In your application, design and implement a common application interface
function, such as:
int GetDBQueryCount(String strQuery)
In any place when you need to get a query result row count, it's convenient
to use this function.
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w

to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||CJM wrote:
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> 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.|||CJM wrote:
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> 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]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OhFw993mGHA.2264@.TK2MSFTNGP04.phx.gbl...
> 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|||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 ski
p
> 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

Sunday, March 25, 2012

Determine if more than one row returned

No comments:

Post a Comment

Determining Security

Blog Archive

About Me