MySQL stored procedure passing array of number in parameters

Question

I'am not sure what I've been doing wrong ...

I have a simple query :

SELECT * FROM locations WHERE (ID IN (1, 2, 3, 4, 5));

now I just took that query and made it into a store procedure with an arguments of type LONGTEXT

sp_FetchMultipleLocations(IN argLocations LONGTEXT)
BEGIN
    SELECT * FROM locations WHERE (ID IN (argLocations));
END;

then I call that stored procedures with multiples values in that parameters :

CALL sp_FetchMultipleLocations('1, 2, 3, 4, 5');

but the IN statement in the where clause doesn't seem to be working, it shows me only the first(1) location ... why is that ?

thanks

Marc B · Accepted Answer · 2015-07-08 15:56:19Z

6

The argument is a single monolithic string. Just because you're using that argument in an IN clause inside the sproc doesn't mean MySQL knows it should tear apart that csv string into individual values - it has no idea what CSV is, nor should it.

You'll have to use dynamic sql, e.g. in pseudo-ish code:

procedure foo(in args longtext)
begin
    sql = concat('SELECT ... WHERE foo IN (', args, ')')
    execute @sql
end

answered Jul 8, 2015 at 15:56

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Rause · Accepted Answer · 2021-05-26 08:30:53Z

2

found this today on this page

SET @c = 'xxx,yyy,zzz';
SELECT * from table 
WHERE FIND_IN_SET(columnname,@c);

works for me.

answered May 26, 2021 at 8:30

Tom Rause

212 bronze badges

Comments

meda · Accepted Answer · 2015-07-08 15:51:19Z

0

You just need to use a split function, do it by comma.

The way you pass it now is a single string.

answered Jul 8, 2015 at 15:51

meda

45.5k14 gold badges96 silver badges123 bronze badges

1 Comment

Sam Over a year ago

Not sure what you mean ? the stored procedure is begin called form a report (crystal report).

Jeff Cashion PhD · Accepted Answer · 2024-07-29 15:51:08Z

So this is a problem that I have had for quite some time, the ability to pass in an array of user ids, emails, or any other array of values, and perform a database operation on all records that match.

For Example:
Let's say an admin user of your website wants to do a bulk-update on the users of of the software, disabling their accounts. Perhaps they were displayed a list of users, and checked the "disable" box next to some of them. Now they click the "Disable Users" button. The slow way to do this is to (on the server-side) iterate through each userId and call some function, such as User.disable(userId).

The more efficient way is to pass in the array of userIds into the database and have it do the operation at once on all desired records. Here is how that is done.

Your stored procedure should look like the following:


-- userIds is a json string of an array of integers, e.g. "[1, 2, 3]"
CREATE PROCEDURE `user.disable`(userIds VARCHAR(4000))
BEGIN
    UPDATE user SET enabled = 0 WHERE JSON_CONTAINS(userIds, id);
END

Your code which calls this might look like the following (PHP):


class User {
    public static function disableMany(array $userIds): bool
    {
        $strUserIds = json_encode($userIds);
        $query = "CALL `user.disable`($strUserIds)";
        $result = mysqli_query($con, $query);
        return $result !== false;
    }
}

This example is using MariDb, see link JSON_CONTAINS.

The MySQL documentation is Here (JSON_CONTAINS).

I hope this helps and good luck.

Collectives™ on Stack Overflow

MySQL stored procedure passing array of number in parameters

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related