Please check this :
SET TERM ^ ;
ALTER PROCEDURE SP_VERSION_NOS (
PORTION smallint,
SEPARATOR char(1),
VERSION_UPTO varchar(80) )
RETURNS (
VERSION_NO varchar(80) )
AS
declare variable i smallint;
declare variable SEPARATOR_POS Integer;
declare variable VERSION_UPTO_PORTION_INT integer;
declare variable VERSION_i varchar(80);
declare variable VERSION_i_PORTION_INT integer;
BEGIN
i = 1;
while (:PORTION > :i) do begin
select position(:SEPARATOR in :VERSION_UPTO) from RDB$DATABASE into
:SEPARATOR_POS;
if(0 != :SEPARATOR_POS) then begin
VERSION_UPTO = substring(:VERSION_UPTO from (:SEPARATOR_POS +
1) for char_length(:VERSION_UPTO));
end
i = 1 + :i;
end
select position(:SEPARATOR in :VERSION_UPTO) from RDB$DATABASE into
:SEPARATOR_POS;
if(0 != :SEPARATOR_POS) then begin
VERSION_UPTO = substring(:VERSION_UPTO from 1 for :SEPARATOR_POS -
1);
end else begin
VERSION_UPTO = substring(:VERSION_UPTO from 1 for
char_length(:VERSION_UPTO));
end
VERSION_UPTO_PORTION_INT = cast(:VERSION_UPTO as integer);
FOR SELECT a.VERSION_NO FROM VERSION_NOS a INTO :VERSION_NO
DO BEGIN
VERSION_i = :VERSION_NO;
i = 1;
while (:PORTION > :i) do begin
select position(:SEPARATOR in :VERSION_i) from RDB$DATABASE
into :SEPARATOR_POS;
if(0 != :SEPARATOR_POS) then begin
VERSION_i = substring(:VERSION_i from (:SEPARATOR_POS + 1)
for char_length(:VERSION_i));
end
i = 1 + :i;
end
select position(:SEPARATOR in :VERSION_i) from RDB$DATABASE into
:SEPARATOR_POS;
if(0 != :SEPARATOR_POS) then begin
VERSION_i = substring(:VERSION_i from 1 for :SEPARATOR_POS - 1);
end else begin
VERSION_i = substring(:VERSION_i from 1 for
char_length(:VERSION_i));
end
VERSION_i_PORTION_INT = cast(:VERSION_i as integer);
if(:VERSION_UPTO_PORTION_INT >= :VERSION_i_PORTION_INT) then begin
SUSPEND;
end
END
/* SELECT p.VERSION_NO FROM SP_VERSION_NOS ('3', '.', '1.23.450.789')
p;*/
END^
SET TERM ; ^
GRANT EXECUTE
ON PROCEDURE SP_VERSION_NOS TO SYSDBA;
