CODEWITHSUNDEEP
mysqlstored-procedures
Shahriar N Khondokar
Shahriar N Khondokar

Reputation: 578

Parameters in MySQL stored procedure

I want to create a MySQL stored procedure (SP) with input parameters.

However, the number of parameters cannot be determined at the time of writing the SP. (The scenario is that the users will have multiple options to choose. The options chosen will form the search criteria: 

select ... 
where prod_category = option1 && option2 && option3 &&...

So, if someone chooses only option1 and option2, only 2 parameters will be sent. Sometimes it may be 50+ options are chosen and hence 50+ parameters will have to be sent.)

So, I have 3 questions: 1. Can I handle such a scenario using MySQL stored procedures (SP)? 2. Is the SP the professional way to handle such scenario? 3. If SP is not the professional way to handle these scenarios, is there anything else that will handle these searches efficiently? The search is the core functionality of my application.

Thanks in advance for any help!

Upvotes: 3

Views: 1037

Answers (2)

NotGaeL
NotGaeL

Reputation: 8484

MySQL stored procedures accept only a fixed number of arguments. You can build your list of parameters and values delimited on a single string parameter and then process them on your procedure, or use your application language to build the query instead.

From http://forums.mysql.com/read.php?98,154749,155001#msg-155001

No, MySQL sprocs accept only a fixed number of arguments. ISO SQL is somewhat optimised for correct RDBMS logic (unless you were to ask EF Codd, CJ Date or Fabian Pascal), but in many ways SQL is quite literal, so if SQL seems to make what you are trying to do very difficult, it could be that your design needs another look, in this case aspects of the design that require repeated multiple ad hoc deletions.

If such deletions are unavoidable, here are three options. First, in an application language build the delete query to include comma-delimited string of IDs. Second, pass such a string to an sproc that PREPAREs the query using such a string. Third, populate a temp table with the target IDs and write a simple join query that deletes the joined IDs.

Upvotes: 2

Alain Collins
Alain Collins

Reputation: 16362

There are lots of great reasons to use stored procedures. Here's an article that lists some. Hopefully that will address the "professionalism" question.

As for the passing of parameters, I don't believe you can have a variable list.

A long time ago, I saw it "done" by writing the values to a table and having the stored procedure read them back in. (Use a session_id in the table and then pass that to the procedure).

As for "efficiency", it depends on your definition. There might be a slight speed benefit to the stored procedures, but I wouldn't worry about that. What did you mean?

Upvotes: 1

Related Questions