Welcome to WebmasterWorld Guest from 184.72.177.182

Forum Moderators: open

Message Too Old, No Replies

Help, problem with stored procedure.

     
4:51 am on Feb 12, 2012 (gmt 0)

Full Member

5+ Year Member

joined:June 30, 2008
posts: 318
votes: 0


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.
10:54 pm on Mar 8, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 0


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.
3:13 am on Mar 9, 2012 (gmt 0)

Full Member

5+ Year Member

joined:June 30, 2008
posts: 318
votes: 0


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?
8:19 pm on Mar 11, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 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)

Full Member

5+ Year Member

joined:June 30, 2008
posts: 318
votes: 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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 0


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.
2:38 am on Mar 12, 2012 (gmt 0)

Full Member

5+ Year Member

joined:June 30, 2008
posts: 318
votes: 0


thanks, i'll look into it.