Help, problem with stored procedure.

[nelsonm]

Hi all,

I have the following store procedure that does not work:

DROP PROCEDURE `usp_selectcount`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_selectcount`(
IN pfrom VARCHAR(50)
)
BEGIN
SELECT COUNT(*) AS count
FROM pfrom;
END

It returns error: #1146 - Table 'irishmistusa-db.pfrom' doesn't exist

It's being called as:
call usp_selectcount("workorder");

I'm passing the name of the table "workorder" through the "pfrom" parameter but "pfrom" it's not getting parsed.

Does anyone know what is going on?

thanks.

[Dijkgraaf]

You can't use a variable in a SQL FROM clause.

You would either have to do a case statement

CASE pfrom
WHEN 'workorder' THEN
SELECT COUNT(*) AS count FROM workorder
WHEN ...
ELSE ...

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.

[nelsonm]

Thanks...

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?

[Dijkgraaf]

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.

[nelsonm]

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.

[Dijkgraaf]

If there is a limited set of tables what you could do is

SELECT 'workorder' as tablename, COUNT(*) AS count FROM workorder
UNION ALL
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.

[nelsonm]

thanks, i'll look into it.