CODEWITHSUNDEEP
sqlstored-proceduresfirebird2.5
Sk1X1
Sk1X1

Reputation: 1373

Create procedure from select query

I need to write a stored procedure in Firebird 2.5. I wrote this sql query:

INSERT INTO A_OBRATYKUMHIST(OBRAT, MONTH, YEAR, SECURITYUSER_ID, FIRM_ID)
select 
    sum(AO.Obrat), 11, 2010, AO.SecurityUser_ID, AO.Firm_ID
from A_OBRATYKUMHIST_TEMP AO
where
    AO.Rok = 2010
    and AO.Mesic <= 11
group by
    AO.SecurityUser_ID, AO.Firm_ID;

My goal is fill table with cumulative sums.

edit 1:

So I wrote this:

SET TERM ^ ;

CREATE OR ALTER PROCEDURE A_TESTIK (
    start_year integer,
    end_year integer)
returns (
    obrat integer,
    mesic integer,
    rok integer)
as
declare variable "YEAR" integer = 2005;
declare variable "MONTH" integer = 1;
begin
    select
        sum(II.localamountwithoutvat), ib_decodedate_month(VatDate$DATE),
            ib_decodedate_month(VatDate$DATE)
    from IssuedInvoices II
    group by
        ib_decodedate_month(VatDate$DATE), ib_decodedate_month(VatDate$DATE)
    into :obrat, :mesic, :rok;
    suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON ISSUEDINVOICES TO PROCEDURE A_TESTIK;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE A_TESTIK TO SYSDBA;

But when I run this, I get error: "Multiple rows in singleton select". How can I fix it?

Upvotes: 0

Views: 291

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109174

If a SELECT returns multiple rows, then you need to use FOR SELECT ... DO ...

Change the body of your stored procedure to:

FOR select
       sum(II.localamountwithoutvat), ib_decodedate_month(VatDate$DATE),
        ib_decodedate_month(VatDate$DATE)
   from IssuedInvoices II
   group by
       ib_decodedate_month(VatDate$DATE), ib_decodedate_month(VatDate$DATE)
   into :obrat, :mesic, :rok
DO
   suspend;

Upvotes: 1

Up_One
Up_One

Reputation: 5271

Here is the Firebird proc syntax : 

CREATE PROCEDURE name [(param1 datatype1, param2 datatype2, ...)] 
[RETURNS (param3 datatype3, param4 datatype4, ...)] 
AS BEGIN
  <body>
END;

The rest is up to you !

Here is some good material about procs in Firebird

Upvotes: 1

Related Questions