----- Reply message -----
Od: "Raith,Daniel" <draith@...>
Do: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Temat: [firebird-support] for select stored procedure question
Data: czw., paź 10, 2013 22:57

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: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Raith,Daniel
Sent: Thursday, October 10, 2013 2:38 PM
To: firebird-support@yahoogroups.com
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: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Woody
Sent: Thursday, October 10, 2013 1:27 PM
To: firebird-support@yahoogroups.com
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]