Stored procedure that accepts multiple parameters

Question

It's my first time working with stored procedures.

The previous developer already had a stored procedure in place that works, but it only accepts 1 parameter.

I am using PHP to pass the parameters:

 <?php
   $containers = $_POST['cntnum'];

   $shortened = array();
   foreach($containers as $short)
   {
     $shortened[] = substr($short, 0, 10);
   }
   $sans_check = preg_replace('/\n$/','',preg_replace('/^\n/','',preg_replace('/[\r\n]+/',"\n",$shortened)));
   $sans = "'" . implode("', '", $sans_check) ."'";

   // At this point, $sans looks like this: 'value1', 'value2', 'value3'... 

   // now I send $sans to the stored procedure
   $thecall = mysqli_query($dbc, "CALL SP_ContSearch_TEST($sans)");
 ?>

I can send 1 value with no problem. I get back the data. But when there are more than 1, I get the following error:

Incorrect number of arguments for PROCEDURE table.storeprocedure; expected 1, got 3

Here is what the stored procedure looks like (shortened for time):

Begin
  DECLARE sans_check varchar(100);  // adjusted from 10, but same error message
  SET sans_check = SUBSTR(cont,1,10);
  SELECT
    `inventory`
    ,delivery_date
    ,pool
  FROM 
    inventory
  WHERE
    CONTAINER_CHECK IN (cont);
  END

The parameter cont is varchar(11) // not sure if that means anything

This is my first attempting a stored procedure call, and I can return data for one value. I need to return data for multiple values.

marc_s · Accepted Answer · 2023-08-08 19:03:51Z

2

The error message is absolutely right. You are sending 3 parameters to a stored procedure which takes only one.

What you've done is you have modified the stored procedure which takes a single string such that it still expects a single string.

You should modify the definition of the stored procedure to take 3 parameters (that part is missing in your question)

Here is an example of a stored procedure declaration with 3 parameters:

 CREATE PROCEDURE SP_ContSearch_TEST
    (IN sans1 CHAR(10),
     IN sans2 CHAR(10),
     IN sans3 CHAR(10)
     -- add as many other parameters here as you need
    )
 BEGIN
     -- your stored proc logic here.. can use sans1, sans2, and sans3
 END

You should also change your code to use parameterized queries instead of the way you're doing right now. See: http://php.net/manual/en/pdo.prepared-statements.php or http://php.net/manual/en/mysqli.prepare.php

edited Aug 8, 2023 at 19:03

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 21, 2017 at 15:28

Mike Dinescu

56k17 gold badges124 silver badges153 bronze badges

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

9 Comments

John Beasley Over a year ago

How would I go about modifying the definition of the stored procedure? It should be able to accept a max of 10 parameters.

Mike Dinescu Over a year ago

@JohnBeasley please see the updated answer for a generic definition with 3 parameters. You should be able to extrapolate to more params. Also, what RDBMS are you using? The syntax might be slightly different in different databases

Claudio Pinto Over a year ago

Don't forget to DROP the existing procedure in you declaration otherwise you'll get an exception.

Brian Over a year ago

If you really need a variable number of arguments you can always look at creating a UDF ... dev.mysql.com/doc/refman/5.7/en/udf-arguments.html however you're then aswell just writing a PHP function... also is the Stored Proc really needed?

John Beasley Over a year ago

Good news and bad news. I recreated the stored procedure as you indicated above, and I can now return data for 10 different values. However...10 values MUST be submitted. The user can enter up to 10, but may enter less. Therefore, I'm kind of back to the drawing board.

|

Collectives™ on Stack Overflow

Stored procedure that accepts multiple parameters

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related