RE: [firebird-support] for select stored procedure question

Thu, 10 Oct 2013 13:58:24 -0700 

I guess I was actually on FB2.5.  After switching back to FB1.5, I think I'm 
out of luck because the SUBSTRING internal function requires integer literals.  
So this statement isn't possible:

substring( input_string from idx-1 for 1 )

Since most of our servers are running 1.5 and I can't control updates, and I 
can't use UDF's for distribution reasons, I think I'll have to do this outside 
of Firebird. Unless I write a substring_proc, hmmm.

From: [email protected] 
[mailto:[email protected]] On Behalf Of Raith,Daniel
Sent: Thursday, October 10, 2013 2:38 PM
To: [email protected]
Subject: RE: [firebird-support] for select stored procedure question


That works, but only if my stored proc contains the suspend.

SET TERM ^ ;

CREATE OR ALTER PROCEDURE INITIAL_CAPS_PROC (
    input_string varchar(100))
returns (
    output_string varchar(100))
as
declare variable input_length integer;
declare variable idx integer;
begin
  output_string = '';
  input_length = char_length(input_string);

  if (input_length > 0) then
  begin

    idx = 1;
    while (idx <= input_length) do
    begin
      if (idx = 1) then
        output_string = upper( substring( input_string from 1 for 1 ) );
      else
      begin
        if ( substring( input_string from idx-1 for 1 ) = ' ' ) then
          output_string = output_string || upper( substring( input_string from 
idx for 1 ) );
        else
          output_string = output_string || lower( substring( input_string from 
idx for 1 ) );
      end

      idx = idx + 1;
    end
  end

  suspend;
end^

SET TERM ; ^

From: [email protected] 
[mailto:[email protected]] On Behalf Of Woody
Sent: Thursday, October 10, 2013 1:27 PM
To: [email protected]
Subject: Re: [firebird-support] for select stored procedure question



As long as the initial_caps_proc doesn't have a suspend statement, it will
basically be seen as a function call. To use it to update records one time:

Update MyTable set Column_A = (select OutVar from
initial_caps_proc(Column_A)), Column_B = (select OutVar from
initial_caps_proc(Column_B));

I haven't tested this but it should work if memory serves me correctly. :)

Woody (TMW)





[Non-text portions of this message have been removed]

Reply via email to