Cannot create new procedure in SQL manager lite

Question

I am working with SQL manager lite for Interbase/Firebird application. I have downloaded firebird database, successfully connected to that database and its host, but now I want to create procedure.

I couldn't done it via tutorials, so I decided to just click New->Procedure and do that automatically. But doing this way I still have errors.

My code what I have tried without clicking New->Procedure:

CREATE PROCEDURE MyProc
AS
  SELECT M_DOKUMENTY.NDZIEN FROM M_DOKUMENTY WHERE M_DOKUMENTY.SRODZAJ = '1234'
GO;

The code which was generated using New->Procedure wizard:

CREATE PROCEDURE SHOW_ALL
AS
BEGIN
  /* Procedure body */
  SELECT 
    M_DOKUMENTY.NDZIEN,
    M_DOKUMENTY.CKIERUNEK,
    M_DOKUMENTY.CMEDIUM FROM M_DOKUMENTY WHERE M_DOKUMENTY.SRODZAJ = '1234'
  SUSPEND;
END;

But when I am clicking that lightning icon (compile) it complains about error:

Dynamic SQL Error.

SQL error code = -104.

Token unknown - line 9, column 3.

SUSPEND.

How to fix that?

Screenshot of error in SQL Manager lite

Notice how using a stored procedure like that would almost prohibit SQL optimizations in Firebird. From FB perspective, procedures are "black boxes" that yield any random data with no predictions or assumptions possible. What you really need is probably CREATE VIEW; Try to make your query into an SP, into a VIEW and then run 3 queries joining some other table 1) with SP; 2) with VIEW; 3) as explicit OUTER JOIN in the same statement. For all three queries read the execution plan and compare. P.S. Firebird is not Microsoft SQL Server. — Arioch 'The

Mark Rotteveel Mark Rotteveel · Accepted Answer · 2019-01-13T08:51:18

The problem is that your syntax is wrong. You need to define the output parameters, and you need to use either select ... into <list of variables> to select a single row, or for select ... into <list of variables> do to loop over multiple rows.

Your stored procedure should be something like:

CREATE PROCEDURE SHOW_ALL
  RETURNS (NDZIEN varchar(50), CKIERUNEK varchar(50), CMEDIUM varchar(50))
AS
BEGIN
  /* Procedure body */
  for SELECT 
        M_DOKUMENTY.NDZIEN,
        M_DOKUMENTY.CKIERUNEK,
        M_DOKUMENTY.CMEDIUM 
      FROM M_DOKUMENTY 
      WHERE M_DOKUMENTY.SRODZAJ = '1234'
      into :NDZIEN, :CKIERUNEK, :CMEDIUM 
  do
    SUSPEND;
END

If your select only produces a single row, then you could also consider using

CREATE PROCEDURE SHOW_ALL
  RETURNS (NDZIEN varchar(50), CKIERUNEK varchar(50), CMEDIUM varchar(50))
AS
BEGIN
  /* Procedure body */
  SELECT 
      M_DOKUMENTY.NDZIEN,
      M_DOKUMENTY.CKIERUNEK,
      M_DOKUMENTY.CMEDIUM 
    FROM M_DOKUMENTY 
    WHERE M_DOKUMENTY.SRODZAJ = '1234'
    into :NDZIEN, :CKIERUNEK, :CMEDIUM;
  SUSPEND;
END

Notice the ; after the into clause. In this case you could also leave out the SUSPEND;. That will make the stored procedure executable instead of selectable. Depending on how you want to use it, that could be a better choice.

See the Firebird documentation on created stored procedures and its procedural SQL language for more information.

Cannot create new procedure in SQL manager lite

1 Answers