| 10:54 pm on Mar 8, 2012 (gmt 0)|
You can't use a variable in a SQL FROM clause.
You would either have to do a case statement
WHEN 'workorder' THEN
SELECT COUNT(*) AS count FROM workorder
Or you have to construct a Dynamic SQL string and execute that (not good practice).
In fact passing a table name to a stored procedure in the first place is a bad idea and probably means you've made a fundamental flaw in you database/application design. Maybe explain what you are trying to achieve in the first place and then someone will suggest a better option.
| 3:13 am on Mar 9, 2012 (gmt 0)|
I looked in the mysql manual but i could not find anything that talks about the restrictions on sql statements in stored procedures.
Does anyone know where i get some info on this?
| 8:19 pm on Mar 11, 2012 (gmt 0)|
It is not a matter of being a restriction, but rather that you trying to do something which just is not a feature, hence it it not documented.
And there is a good reason that it isn't a feature as dynamically executing SQL from a parameter passed to a stored procedure opens you up to SQL injection attacks.
And don't think that nobody will ever do that to your site as there was just such an attack that hit thousands of sites recently.
Again, explain what your end objective is at a higher level, and someone will probably suggest a better way of doing things.
| 11:33 pm on Mar 11, 2012 (gmt 0)|
The end objective was to use a single stored procedure to count the records in tables. This count would be used to determine the number of grid pages available for display in the pager section of the jqGrid plugin navigation panel.
since parameter variables are not allowed to be used for table names in stored procedures, the count query mysql statements can stay in my php crud scripts.
| 2:28 am on Mar 12, 2012 (gmt 0)|
If there is a limited set of tables what you could do is
SELECT 'workorder' as tablename, COUNT(*) AS count FROM workorder
SELECT 'table2' as tablename, COUNT(*) AS count FROM table2;
and have your application select the rows as needed from the results. This way you don't need to pass a parameter.
| 2:38 am on Mar 12, 2012 (gmt 0)|
thanks, i'll look into it.