problem with select

SQL Apprentice Question
I'm trying to do a select and I'm having a problem with it (code below)


declare @teste_varchar2 as varchar(20)
declare @teste_varchar as varchar(500)


set @teste_varchar2 = "valor_fact"
exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' +
@cont_descCursor)


What is odd with the above code is that if I use a similar code but not
dynamic sql it works.


select valor_fact from ##CONTENC where contracto = @cont_descCursor


Celko Answers
>> I'm trying to do a select and I'm having a problem with it (code below) <<


Oh yes, the old "Britney Spears, Automobile and Squid" code Module!!

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.