ADO - T-SQL - Stored Proc Question

[Neil Whitlow]

I am new to T-SQL and SQL Server, but not new to VB, ADO, and SQL in general.

My latest quandary:

I am trying to do this all in a stored proc to minimize network traffic by
reducing the amount of times my app calls a stored procedure, gets 0 rows
for a result, then calls another stored proc based on that 0 return.

Query 1 could possibly return some "custom" business rule based on the parameters
to my stored proc (custom conditions are not frequently met, so majority
of time, Query 1 will return zero rows. It must be checked first though.

Query 2 will retrieve the "default" business rule that is to be used ONLY
if no "custom" rules were found with Query 1.


<Query 1>
if @@ROWCOUNT = 0
Begin
<Query 2>
End


My ROWCOUNT test works perfectly. In most cases, it will find 0 rows for
Query 1, then it will properly go and execute Query 2.

My problem is that the ADO recordset shows .BOF and .EOF, even though Query
2 returns 4 rows.

I look at this in SQL Server Query Analyzer and get:

Query 1 - Zero rows affected
Query 2 - 4 rows affected

I can visually see in the "Grids" section of query analyzer that the stored
procedure is generating two resultests. I guess the ADO recordset can't
see past the first one.

This is not worth getting into "Data Shaping", especially since I wanted
to increase performance, not get more complicated structures/providers with
this to return cubes of resultsets and stuff.

What I cannot find in T-SQL docs is a way to instruct the proc to "discard"
a resultest that it generated. If there is a way to do that, then I could
"clear" the results if Query 1 has zero results, then the Query 2 resultset
could be seen by the ADO recordset in my app (I guess).

If anyone can shed some light on this one, I would be most appreciative.
Hopefully if there is not a way to stop the stored proc from returning two
recordsets, then there is a way to get at the second one with the ADO recordset
without resorting to "Shaping" or something nasty like that.

Neil Whitlow

[Simon Sellick]

"Neil Whitlow" <neil_whitlow@agla.com> wrote:
>
>I am new to T-SQL and SQL Server, but not new to VB, ADO, and SQL in general.
>
>My latest quandary:
>
>I am trying to do this all in a stored proc to minimize network traffic
by
>reducing the amount of times my app calls a stored procedure, gets 0 rows
>for a result, then calls another stored proc based on that 0 return.
>
>Query 1 could possibly return some "custom" business rule based on the parameters
>to my stored proc (custom conditions are not frequently met, so majority
>of time, Query 1 will return zero rows. It must be checked first though.
>
>Query 2 will retrieve the "default" business rule that is to be used ONLY
>if no "custom" rules were found with Query 1.
>
>
><Query 1>
>if @@ROWCOUNT = 0
> Begin
> <Query 2>
> End
>
>
>My ROWCOUNT test works perfectly. In most cases, it will find 0 rows for
>Query 1, then it will properly go and execute Query 2.
>
>My problem is that the ADO recordset shows .BOF and .EOF, even though Query
>2 returns 4 rows.
>
>I look at this in SQL Server Query Analyzer and get:
>
>Query 1 - Zero rows affected
>Query 2 - 4 rows affected
>
>I can visually see in the "Grids" section of query analyzer that the stored
>procedure is generating two resultests. I guess the ADO recordset can't
>see past the first one.
>
>This is not worth getting into "Data Shaping", especially since I wanted
>to increase performance, not get more complicated structures/providers with
>this to return cubes of resultsets and stuff.
>
>What I cannot find in T-SQL docs is a way to instruct the proc to "discard"
>a resultest that it generated. If there is a way to do that, then I could
>"clear" the results if Query 1 has zero results, then the Query 2 resultset
>could be seen by the ADO recordset in my app (I guess).
>
>If anyone can shed some light on this one, I would be most appreciative.
> Hopefully if there is not a way to stop the stored proc from returning
two
>recordsets, then there is a way to get at the second one with the ADO recordset
>without resorting to "Shaping" or something nasty like that.
>
>Neil Whitlow
>
>
Neil,

As you say, it isn't really the answer, but I believe there is a NextRecordSet
method in an ADO control - perhaps that would help?

Simon.

[John McQuilling]

The problem is that your proc may return one or two result sets,
which is hard to handle. It is better to design the proc to return only one
result set.

One way to handle this in T_SQL is to define a temp table which has the structure
for the result set. Then insert into the table with query 1 and if there
are no rows insert into the table with query 2. Finally do a select * from
the temp table.

Another approach is to check the first query with a if exists select * from
..... This does add some overhead but the data should be cached if you have
to retrieve later. The code would be if exists (select * from ...)
Query 1
else
Query 2

Previous message
=======================================
"Simon Sellick" <simon.sellick@tesco.net> wrote:
>
>"Neil Whitlow" <neil_whitlow@agla.com> wrote:
>>
>>I am new to T-SQL and SQL Server, but not new to VB, ADO, and SQL in general.
>>
>>My latest quandary:
>>
>>I am trying to do this all in a stored proc to minimize network traffic
>by
>>reducing the amount of times my app calls a stored procedure, gets 0 rows
>>for a result, then calls another stored proc based on that 0 return.
>>
>>Query 1 could possibly return some "custom" business rule based on the
parameters
>>to my stored proc (custom conditions are not frequently met, so majority
>>of time, Query 1 will return zero rows. It must be checked first though.
>>
>>Query 2 will retrieve the "default" business rule that is to be used ONLY
>>if no "custom" rules were found with Query 1.
>>
>>
>><Query 1>
>>if @@ROWCOUNT = 0
>> Begin
>> <Query 2>
>> End
>>
>>
>>My ROWCOUNT test works perfectly. In most cases, it will find 0 rows for
>>Query 1, then it will properly go and execute Query 2.
>>
>>My problem is that the ADO recordset shows .BOF and .EOF, even though Query
>>2 returns 4 rows.
>>
>>I look at this in SQL Server Query Analyzer and get:
>>
>>Query 1 - Zero rows affected
>>Query 2 - 4 rows affected
>>
>>I can visually see in the "Grids" section of query analyzer that the stored
>>procedure is generating two resultests. I guess the ADO recordset can't
>>see past the first one.
>>
>>This is not worth getting into "Data Shaping", especially since I wanted
>>to increase performance, not get more complicated structures/providers
with
>>this to return cubes of resultsets and stuff.
>>
>>What I cannot find in T-SQL docs is a way to instruct the proc to "discard"
>>a resultest that it generated. If there is a way to do that, then I could
>>"clear" the results if Query 1 has zero results, then the Query 2 resultset
>>could be seen by the ADO recordset in my app (I guess).
>>
>>If anyone can shed some light on this one, I would be most appreciative.
>> Hopefully if there is not a way to stop the stored proc from returning
>two
>>recordsets, then there is a way to get at the second one with the ADO recordset
>>without resorting to "Shaping" or something nasty like that.
>>
>>Neil Whitlow
>>
>>
>Neil,
>
>As you say, it isn't really the answer, but I believe there is a NextRecordSet
>method in an ADO control - perhaps that would help?
>
>Simon.