Geek Answers Handbook

Firebird stored procedure to concatenate all field values ​​from multiple lines

My goal is to write a saved process that can collect all field values ​​from several rows into one single output variable (possibly varchar (some_length)). This may seem like a strange solution, but I'm pretty sure that this is the only thing I can use in this situation. I have not used Firebird before, and the stored processes looked different than in other well-known db systems. My Firebird is 1.5 and dialect 3 (not sure what that means). So maybe someone can help me with an example algorithm.

+5
source share
3 answers

The following procedure describes:

SET TERM !!;
CREATE PROCEDURE concat_names
  RETURNS (concat VARCHAR(2000))
AS
DECLARE VARIABLE name VARCHAR(100);
BEGIN
  concat = '';
  FOR SELECT first_name || ' ' || last_name FROM employee INTO :name
  DO BEGIN
    concat = concat || name || ', ';
  END
END!!
SET TERM ;!!
EXECUTE PROCEDURE concat_names;

But I doubt the wisdom of this decision. How do you know that VARCHAR is long enough for all rows in your desired dataset?

It is much easier and safer to run a query to return the result to the application line by line. Each application programming language has string concatenation methods, but more importantly, they have more flexible methods for managing data growth.

By the way, the “dialect” in Firebird and InterBase refers to the compatibility mode that was introduced so that applications developed for InterBase 5.x can work with later versions of InterBase and Firebird. That was almost ten years ago, and AFAIK does not need to use anything below dialect 3 today.

+5
source

, :

    CREATE PROCEDURE CONCAT(
    F1 VARCHAR(385),
    F2 VARCHAR(385),
    SEPARATOR VARCHAR(10))
RETURNS (
    RESULT VARCHAR(780))
AS
begin

  if ((:f1 is not null) and (:f1 <> '')) then
    result = :f1;

  if ((:f2 is not null) and (:f2 <> '')) then
    if ((result is not null) and (result <> '')) then
      begin
        if ((:separator is not null) and (separator <> '')) then
          result = result||separator||f2;
        else
          result = result||f2;
      end
    else
      result = f2;

  suspend;
end
0

Returning multiple lines using Firebird stored procedures is very simple.

Do not use:

execute procedure proc_name(value);

Use instead:

select * from proc_name(value);
0
source

More articles:


All Articles