Description:
MySQL stored procedures are difficult to use with typed datasets because most of the standard ways of setting them up are buggy (see below for details). The parameters for MySQL stored procedures do not appear in Server Explorer and the standard wizards for creating queries do not work with MySQL stored procedures unless they have zero parameters.
How to repeat:
Create a stored procedure in a MySQL database which takes at least one parameter.
In Visual Studio 2008:
1. Add the database to the Server Explorer in a Visual Studio project
2. Find the stored procedure in the Server Explorer window and attempt to expand it using the + sign. *Sometimes* (I cannot always reproduce this) an error message "column ROUTINE_CATALOG does not belong to the table Procedure Parameters" is displayed in a pop-up window and no parameters are displayed.
3. In Add a dataset to the project (Add new item, Dataset).
4. Add a table to the dataset (drag and drop an existing MySQL table from the database shown in Server Explorer).
5. Add a query to the table (right click tableAdapter, Add Query).
6. Select "Use existing stored procedure", Next
7. Select the stored procedure from the drop down list. The wizard ends abruptly without any error message and the query is not added to the tableAdapter.
Suggested fix:
Workaround:
Add the stored procedure manually:
6. Select "Use SQL statements", Next, "Select which returns rows". Use the Query Builder to create SELECT * FROM <table name> WHERE <column_name>=<parameter_name>. Test it works by pressing Execute and entering a valid parameter value. The query results will display correctly. Click OK then Next then Next then Finish. The query is created OK."
7. Amend the query from the Properties window: change CommandType to StoredProcedure, change CommandText to the relevant stored procedure, add the parameter to the ParametersCollection
