At 09:37 PM 07-09-02 +0200, you wrote:

>Hi everyone!
>
>I have got the following problem. I need to delete all rows in a table which
>do not have a value (in one of the columns) equal to one of a list of values.
>Then, the problem is how to work with the list of values.
>
>The stored procedure that does the work of deleting those rows, receives a
>varchar(255) with a list of comma separated integer values, in the form of
>"1,2,3" and so on. Let's call the parameter P_LIST.
>
>Then, I would like to execute a SQL statement like this:
>
> DELETE FROM TABLE
> WHERE (FIELD_A = :P_PARAM)
> AND (FIELD_B <> 1)
> AND (FIELD_B <> 2)
> AND (FIELD_B <> 3);
>
>So, I was just trying to find a good way to solve this problem. I have
>thought
>about using arrays, but it seems I cannot make this:
>
>DECLARE VARIABLE LIST INTEGER[50];
>
>Also, it seems I cannot create a domain INT_ARRAY_50, consisting of an array
>of 50 integers, and then try to declare a variable of that type inside the
>stored procedure.
>
>So, the only solutions it seems I have is to use DSQL to dynamically handle
>the comma sepparated values and add "AND (FIELD_B <> XX)" clausules to the
>statement. Then execute it.
>
>For that purpose, I was looking for any sort of function similar to Php's
>"split (separator, string)", which creates an array with the elements of the
>string, using the "separator" separator. I haven't found it, so I will try to
>create it for myself.
>
>Any hints on this? Is there any way to work with arrays or should I just
>forget about arrays and try the DSQL way?

No, there is no way to work with arrays to do this job. Consider having
your client app pass the members of your list to a temp table designed for
the purpose. By temp table I mean one which you keep all the time and use
to store a row for each parameter value you need in the SP - with some
context mechanism to identify the rows your app is interested right now - a
generator is good for this...get the value across to the client before you
begin storing the parameters.

Pass the context id to the stored proc and, inside your proc, use a FOR
SELECT loop to iterate through the set of rows and pick up each parameter
value in turn.

Once the loop is complete, have your SP delete all of the temp rows created
for your context, like

CREATE PROCEDURE XXYY (CONTEXT INTEGER, ......)
AS
...
DECLARE VARIABLE PARAM SOMETYPE;
...
BEGIN
FOR SELECT APARAM FROM TEMPTABLE
WHERE CONTEXTID = :CONTEXT
INTO :PARAM DO
BEGIN
DELETE FROM TABLE
WHERE (FIELD_A = PARAM)
AND (FIELD_B <> 1)
AND (FIELD_B <> 2)
AND (FIELD_B <> 3);
...
END
...
DELETE FROM TEMPTABLE
WHERE CONTEXTID = :CONTEXT;
END

heLen