Continue monolog....

I have found a few news groups posts that dealt with this subject. One solution
proposed to use LIKE keyword, but this has some downsides to it. Anyhow, I wrote
this small SP that could be used for this purpose:

CREATE PROCEDURE PROC_TEST (
M_LIST VARCHAR (80))
RETURNS (
R_ID INTEGER)
AS
declare variable iListLength integer;
declare variable i integer;
declare variable iBeginString integer;
declare variable iIDLength integer;
BEGIN
iListLength = strlen(:m_list);
if (iListLength >= 1) then
begin
iBeginString = 1;
i = 1;
while (i < iListLength) do
begin
if (substrlen(:M_LIST,i,1) = ',') then
begin
R_ID = cast(substr(:M_LIST,iBeginString,i - 1) as integer);
iBeginString = i + 1;
SUSPEND;
end
i = i + 1;
end
--do the last id
R_ID = cast(substr(:M_LIST,iBeginString,iListLength) as integer);
SUSPEND;
end
if (iListLength = 0) then
begin
R_ID = 0;
SUSPEND;
end
END


And here is the usage:

SELECT *
FROM
TEST2
INNER JOIN proc_test('1,2,10,55,67') ON (TEST2.PKFIELD = PROC_TEST.R_ID)


Of course no solution is perfect. With this one I have run into issues regarding
the 80 characters limit on the substr and substrlen functions. I tried
increasing this in the function declaration, but no matter what I increase it to
I always get an overflow error when I pass more then 80 char to it. Has anybody
experienced the same issue with substr and substrlen?

Raf



Rafael Szuminski wrote:

> One possible way I have found is to create a stored procedure that will accept
> the list of ids and the suspend each one of them and then do a inner join
> between the table and the sp, but I can't figure out the syntax for taking a
> string and splitting it based on a delimiter inside the sp....
>
>
> Rafael Szuminski wrote:
>
>
>>Hi,
>>
>>Question:
>>
>>What is the preferred way to pass a variable list of record IDs to a stored
>>procedure and then return records that are based on the list?
>>
>>I have tried the usual suspects: Select IN (:list_of_ids) etc. :-)
>>
>>Here is some background on the issue and perhaps there is a better design solution:
>>
>>We are trying to port an mssql db to firebird. The overall port is simple but
>>for one thing: we have a master/detail level relationship between databases and
>>tables in those databases (I know it’s a bad thing, but there are multiple
>>business requirements that mandate this design, or better put it’s the lesser of
>>the evils). Anyhow, since FB can’t do cross-database joins, we figured we will
>>have our client app query the master database for a list of Ids and then pass
>>that list to the second database as a string or something similar and basically
>>get the recordset that way.
>>
>>So, is there a better way to do this? If not, how can this be done?
>>
>>Thanks in advance
>>
>>Raf
>>
>>
>>
>>
>>Yahoo! Groups Links
>>
>>
>>
>>
>>
>>
>
>

--
Rafael Szuminski
Email:raf@...
Phone:(949)939 - 2458
www.BDCSoftware.com