| Subject | RE: [IBDI] Stored Procedure-Dataset |
|---|---|
| Author | Helen Borrie |
| Post date | 2000-10-24T01:05:03Z |
At 07:39 PM 23-10-00 -0400, you wrote:
RETURNS(Value1 <datatype>, Value2 <datatype>....) will return a one-row set
of output parameters when you execute it and nothing when you select from it.
To get a dataset that potentially returns multiple rows, your proc needs a
FOR..SELECT Col1, Col2, Col3...
from ....
...
into :var1, :var2, :var3...
DO
BEGIN
...
...
SUSPEND
END
construction. :var1, :var2, :var3, etc. must be declared in vv. 5.5 and
higher. You have to SELECT from this style of procedure, not EXECUTE it.
In previous versions, this construct would return a dataset but in list
discussions it appears this was a bug or loophole, viz.
FOR SELECT Col1, Col2, Col3...
FROM <table, view or another SP>
DO
BEGIN
...
...
SUSPEND;
END
It would return rows containing the selected columns, i.e. it was like a
"programmable view". I never did find out why it was tightened out.
Helen
InterBase Developer Initiative ยท http://www.interbase2000.org
___________________________________________________
>Robert,To add to this and attempt to answer the original question (maybe):
>
>Unlike "standard" SPs, dataset SP's (Sp's which return a dataset) must
>be accessed using a SELECT, instead of EXECUTE PROCEDURE.
>
>In fact, a dataset SP can be treated in the same manner you would a
>Table/View, SELECT OutputParamA, OutputParamB FROM SOME_PROCEDURE(
>InputParam1, InputParam2, ...) WHERE ... ORDER BY ...
>
>
>Sean
RETURNS(Value1 <datatype>, Value2 <datatype>....) will return a one-row set
of output parameters when you execute it and nothing when you select from it.
To get a dataset that potentially returns multiple rows, your proc needs a
FOR..SELECT Col1, Col2, Col3...
from ....
...
into :var1, :var2, :var3...
DO
BEGIN
...
...
SUSPEND
END
construction. :var1, :var2, :var3, etc. must be declared in vv. 5.5 and
higher. You have to SELECT from this style of procedure, not EXECUTE it.
In previous versions, this construct would return a dataset but in list
discussions it appears this was a bug or loophole, viz.
FOR SELECT Col1, Col2, Col3...
FROM <table, view or another SP>
DO
BEGIN
...
...
SUSPEND;
END
It would return rows containing the selected columns, i.e. it was like a
"programmable view". I never did find out why it was tightened out.
Helen
> > -----Original Message-----All for Open and Open for All
> > From: Robert F. Tulloch [mailto:tultalk@...]
> > Sent: Monday, October 23, 2000 4:19 PM
> > To: IBDI@egroups.com
> > Subject: [IBDI] Stored Procedure-Dataset
> >
> >
> > Hi:
> >
> > In stored proc returning dataset do you have to declare all the
> > fields returned in Returning or does it just return the dataset?
> >
> > Thanks.
> >
> > Best regards
> >
> > Community email addresses:
> > Post message: IBDI@onelist.com
> > Subscribe: IBDI-subscribe@onelist.com
> > Unsubscribe: IBDI-unsubscribe@onelist.com
> > List owner: IBDI-owner@onelist.com
> >
> > Shortcut URL to this page:
> > http://www.onelist.com/community/IBDI
> >
>
>Community email addresses:
> Post message: IBDI@onelist.com
> Subscribe: IBDI-subscribe@onelist.com
> Unsubscribe: IBDI-unsubscribe@onelist.com
> List owner: IBDI-owner@onelist.com
>
>Shortcut URL to this page:
> http://www.onelist.com/community/IBDI
InterBase Developer Initiative ยท http://www.interbase2000.org
___________________________________________________