limit number of parameters in a stored procedure

[GuardianOfTheFlame]

Hi all,
is there a limit to the number of parameters in a stored procedure?

I have an engine that generate a "CREATE PROCEDURE" script that receives in input a list of columns.
I cannot find a way to pass an array parameter in MySQL, so the XSLT must create a parameter for each column:

CREATE PROCEDURE `sp_test`(IN Col1 VARCHAR(255), IN Col2 VARCHAR(255), IN Col3 VARCHAR(255), ...)

Potentially, the number of columns could be hundreds so I must know if there is a maximum number of parameters.

Thanks,
Matteo

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[r937]

potentially the number of columns could be hundreds????

whoa, i've never seen anything like this in the wild

what type of application is it for?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[GuardianOfTheFlame]

the engine receives an XML in input that contains the definition of a study formed by more questionnaires.
The engine is used to publish a site (in php) and to create the database in which store the answers.

There are several XSL trasformations that create php files and SQL scripts (to create tables, triggers, functions and stored procedures).
Each questionnaire becomes a form (composed by more pages) in PHP and a table in the database.
Questionnaire is composed by questions that become columns of the table and there is no limit to the number of questions (I don't think that there will be more than 50-100 questions, but I cannot be sure).
Each table has its triggers and procedures automatically generated; the procedure I mentioned above is used to insert form data in the table.

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[r937]

sounds like each question should be a row, not a column

is this your database design, or some sort of application software that you inherited?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[GuardianOfTheFlame]

it's our database design, but I describe it in a quick way; it's more complicated.
Now we used the system to generate clynical studies so every questionnaire is a visit.
There are several doctors that logon to the study's site and each doctor has more patients to visit. Every row is the patient situation at a specific visit.

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[r937]

i still think you should normalize the data

having multiple similar columns breaks the spirit (if not the letter) of first normal form

normalization results in databases that are a lot easier to use

however, if you have already written a whack of triggers and procedures for the existing tables, i can easily see how you would be reluctant to change the design now

all i can say is good luck



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[GuardianOfTheFlame]

mmm... maybe I've done the wrong example; however the columns are not similar: they have different meanings and types (number, varchar, text, enum)
Every visit can track different values so the columns for the first visit aren't the same of the column in the second visit.

Now I have data for the first study and I see that the first visit has 152 fields.

However in order to test the limit, I've tried to create a stored procedure with 1000 TEXT parameters and no error occured so I think I could be quiet sure that there will be no problem about the number of columns

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)