stored procedure with table as a variable

SQL Apprentice Question
I have multiple tables which all have similar structures. A 1 to 2
character CODE and a 10 to 75 character DETAIL. I want a stored
procedure that will take the code and table to find it in as input and
return the detail. Here is my code so far.

CREATE PROCEDURE [dbo].[lookup_code] @mCODE char(2), @mTABLE char(75) as
set nocount on
SELECT detail FROM @mTABLE WITH (READPAST, XLOCK)
where code = @mCODE
GO


Check Syntax returns: Error 156: Incorrect syntax near the keyword 'WITH'.


If found that it doesn't like a variable being used as the table name.
If I change @mTABLE to mytable it passes syntax checking. Can anyone
tell me how to get the table name to be a variable?



Celko Answers
>> I have multiple tables which all have similar structures. A 1 to 2


character CODE and a 10 to 75 character DETAIL. <<

Code? What kind of code? likewise detail is too vague to be a valid
data element name.



>> Can anyone tell me how to get the table name to be a variable? <<


The short answer is use slow, proprietrary dynamic SQL to kludge a
query together on the fly with your table name in the FROM clause.

The right answer is never pass a table name as a parameter. You need
to understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A
model of a set of entities or relationships. EACH TABLE SHOULD BE A
DIFFERENT KIND OF ENTITY. When you have many tables that model the same
entity, then you have a magnetic tape file system written in SQL, and
not an RDBMS at all.


If the tables are different, then having a generic procedure which
works equally on automobiles, octopi or Britney Spear's discology is
saying that your application is a disaster of design.


1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.


2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember
the basics of Software Engineering? Modules need weak coupling and
strong cohesion, etc. This is far more fundamental than just SQL; it
has to do with learning to programming at all.


3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw. Look up the term
attribute splitting.


4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.


Table look-ups are done with a join, not a procedure in SQL. You are
still thinking and writing procedural code in whatever your first
programming language is.

>> FieldName, FieldValue.. <<


We are dealing with a farm? Again, back to basics; fields and columns
are totally different concepts.


>> What are your thoughts on these sorts of approaches, <<


Same as everyone else thinks; it sucks. That design flaw is called
either OTLT (One True Look-up Table) or a MUCK (Massively Unified Code
Key). Google it for all the articles denouncing it.


>> what alternative would you suggest? <<


Since each encoding is independent of all the others and is a different
of kind, then it goes in its own table. That is simple, RDBMS basics.

You do not create a "ZipCode_ICD-9_race_sex_ ..insert 100's of other
encoding names" table. If for no other reason, the data-element name
of the nightmare when you try be accurate and follow ISO-11179 rules.
The best you coudl do is a meta-data name to spotlight the lack of
proper schema design.


Newbies who confuse fields and columns can get caught in the MUCK.
Why? A column has meaning in and of itself while field gets its
meaning from the application program reading it.




Tables do not "overlap"; the whole point of normalization and data
modeling is to avoid redundancy. We seek "one fact, one place, one
time, one way" in the schema.


Throw out your non-design and start over; this is sooooo flawed that
you are going to be writing SP kludges for everything.