Msg#: 4416748 posted 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
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.
Msg#: 4416748 posted 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.
Msg#: 4416748 posted 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.