ADO - T-SQL - Stored Proc Question


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: ADO - T-SQL - Stored Proc Question

  1. #1
    Neil Whitlow Guest

    ADO - T-SQL - Stored Proc Question


    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



  2. #2
    Simon Sellick Guest

    Re: ADO - T-SQL - Stored Proc Question


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

  3. #3
    John McQuilling Guest

    Re: ADO - T-SQL - Stored Proc Question


    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.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center