| Subject | Re: [firebird-support] Re: How To: Write a Store Proc. for the following |
|---|---|
| Author | Tim Ledgerwood |
| Post date | 2004-04-19T08:18:06Z |
At 09:05 AM 19/04/2004, you wrote:
although there are some practical difficulties as far as I can see.
Firstly, it seems, from your description, that what he wants to do is
return a result set of result sets. OK - but how, exactly, does he wish to
display this? My experience is that there are some ways of returning what
he wants - in Firebird, I would use a nested cursor, or a nested FOR loop.
The problem with both of those is that they would return ALL the data to
you, and you would have to sort it out on the client side, although some
reporting tools will allow you to decode the data "automatically" - in
other words, a new section of the report is generated when a particular
field value changes - IIRC, Crystal Reports works this way.
Notice that, in my experience, it doesn't matter what RDBMS you use, your
would still get this big block of data that you would need to sort at the
client side for display.
What I would do is call a query in a loop on the client side. For each
result returned by the query, I would run a stored procedure to return the
result set I am interested in. Each one of these result sets from the
Stored Procedure would form a section of the report.
All of the reporting tools I have seen can be used in this way, and some of
them can be used to display the result set of nested cursors or FOR loops.
HTH
Tim
[Non-text portions of this message have been removed]
>I know, but he wants to use Report Manager and to define a dataset inWell, I don't think that what he requires is particularly difficult,
>a report consisting just in a SQL select from a selectable stored
>procedure which do all the work, including expression evaluation, if I
>understand correctly.
>
>So he need this in this stored procedure:
>-retrieve a row with some columns, based on a parameter
>-retrieve a child row, with a template column
>-merge values from first query columns using the template from second
>query and return the result
>
>and all these in a SP.
>at client side he can do the last step in just one line of code, but
>he needs to be done in a SP
>
>Is this correct?
although there are some practical difficulties as far as I can see.
Firstly, it seems, from your description, that what he wants to do is
return a result set of result sets. OK - but how, exactly, does he wish to
display this? My experience is that there are some ways of returning what
he wants - in Firebird, I would use a nested cursor, or a nested FOR loop.
The problem with both of those is that they would return ALL the data to
you, and you would have to sort it out on the client side, although some
reporting tools will allow you to decode the data "automatically" - in
other words, a new section of the report is generated when a particular
field value changes - IIRC, Crystal Reports works this way.
Notice that, in my experience, it doesn't matter what RDBMS you use, your
would still get this big block of data that you would need to sort at the
client side for display.
What I would do is call a query in a loop on the client side. For each
result returned by the query, I would run a stored procedure to return the
result set I am interested in. Each one of these result sets from the
Stored Procedure would form a section of the report.
All of the reporting tools I have seen can be used in this way, and some of
them can be used to display the result set of nested cursors or FOR loops.
HTH
Tim
[Non-text portions of this message have been removed]