At 04:18 PM 17/04/2004 +0530, you wrote:

>Hi all
>
>I am pretty new to RDBMS's and come from a Visual FoxPro background. I
>would like to write a Stored Procedure that I can use the results in Report
>Manager in such a manner:
>
>SELECT NarrationField FROM MY_STOREDPROCEDURE(:iID, :cFlag)
>
>I will be sending 2 parameters 1st will be the ID to which the record has
>to belong and 2nd a Flag so that I can do a CASE statement in the stored
>proc. to execute the related SELECT statement.

PSQL doesn't have a CASE construct. You can do it with multiple IF statements.

>The resulting cursor I will
>then convert to a narration using another table meant to help generate
>narrations, something like this pseudo / FoxPro code:
>
>PARAMETERS tiID, tcFlag
>
>lcRetVal
>
>DO CASE
> CASE tcFlag = "X"
> SELECT xc.fld1, xc.fld2, xc.fld3 FROM xchild xc WHERE
>xc.iPID = tiID INTO CURSOR xrow
>
> SELECT n.NarrTemplate FROM NarrMaster n WHERE n =
>"XCHILDTEMPLATE" INTO CURSOR xnarr
>
> now xnarr.NarrTemplate will contain something like this
>"'Bill no. ' + xrow.fld1 + ' Bill dt. ' + xrow.fld2 + 'so on'"
>
> lcRetVal = translated xnarr.NarrTemplate in VFP I would
>assign it to a variable and macro substitute it.
>
> CASE tcFlag = "Y"
>...
>...
> OTHERWISE
> lcRetVal = "Unknown Flag can't create narrations"
>ENDCASE
>
>RETURN lcRetVal

You'll need sthg like this:
SET TERM ^;
CREATE MY_SP (
TIID INTEGER,
TCFLAG CHAR)
RETURNS (LCRETVAL VARCHAR(80))
AS
DECLARE VARIABLE ERRMSG VARCHAR(40) = 'Unknown Flag can't create narrations';
BEGIN
IF (TCFLAG = 'X') THEN
FOR SELECT
'Bill no. ' ||fld1||' Bill dt. '||fld2||'so on'
FROM XCHILD
WHERE iPID = :TTID
INTO :LCRETVAL
DO
BEGIN
IF (LCRETVAL IS NULL) THEN
LCRETVAL = ERRMSG;
ELSE
IF (EXISTS (
SELECT NarrTemplate FROM NarrMaster
WHERE n = :LCRETVAL) ) THEN
SUSPEND;
END
ELSE
IF (TCFLAG = 'Y') THEN
BEGIN
....
END
END
COMMIT ^
SET TERM ;^

/hb