Hello Everyone and thanks for your help in advance. I have not worked with stored procedures a great deal and have run into a problem I am not sure how to solve. I have multiple tables within a database that are identical in layout, but have different data that needs to remain in separate tables. However, the methods of accessing these tables are the same. My question is, does each table need a separate sproc or can one sproc be developed, a parameter passed to indicate which table to use. If so, how? Any help would be greatly appreciated. Thanks.

The rows in a properly designed table contain attributes of a business entity that each row in the table models. If you have two tables which are identical in structure, then they almost assuredly model the same thing, and so should be combined into the same table. If you can discern which of two tables contains the entity you are looking for, then you should embody that which distingushes them from one another into another attribute (column) of the same table.
Why do they have to be in separate tables?

The only way to do what you ask in a stored procedure is to use dynamic SQL. That is, you would pass the table name in as a character string parameter, then build up the SQL string by concatenating the parameter(table name) with the rest of the SQL statement, then execute the resultant local string variable.

This is slow, and is a severe security risk, as the stored procedure opens your system up to an SQL injection attack. Furthermore, using a stored procedure is an efficient operation because the stored procedure is compiled once when it is first executed and then the query plan is cached so that it can be quickly re-utilized on subsequent calls. Using dynamic SQL guarantees that this efficiency cannot be realized.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff,

Thanks for the response. One of the primary reasons for having separate tables is that, if combined, the one table would likely be in excess of 200,000 rows. I suspect that this would also cause a severe performance issue as well. Any thoughts?

200,000 rows is peanuts. Any reasonably configured server can handle that with ease.

You create a table because it models something in the real world. The more your table structure deviates from that model, the more difficulties you will encounter trying to use it.

Performance issues can usually be easily addressed by throwing (cheap) hardware at the problem. Design deficiencies can never be solved cheaply.

(I was going to add the word 'almost' to that last sentence, but on second thought removed it, as I think no redesign is ever cheap ;))



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com