|
|
Posted by --CELKO-- on 11/24/06 04:33
>> How to build a procedure that returns different numbers of columns as a result based on a parameter. <<
Don't program like this; it is slow, very dangrous, a bitch to
maintain and violates some of basic ideas of RDBMS. Go back to the
foundations of good SQL programming:
What is a query? A statement that returns a table.
What is a table? A set of entities of the same kind.
What is an entity? A fixed set of known attributes.
What you have is a "magically jellyfish" that changes shape
>> We have analyzed our application and found out that most of the time not all the columns are used. <<
SQL is not an application language; it is a data retrieval language.
Period.
>> So the bigger sets contain the smaller ones. <<
That is why we use VIEWs most of the time, but this might be better
done with three separate queries.
>> Please keep reading all the way to the bottom to better understand technically what we are trying. <<
Well, you start by confusing rows and records.
>> Please note that the passing parameter we can either pass a Unique Identifier (PK) to retrieve a single record [sic], or if we pass for example -1 or NULL we retrieve all the employee records [sic]. <<
Did you ever have a software engineering course? Probably not, or you
would remember the time spent on coupling and cohesion and why
overloaded parameters are bad. Which is it - minus one or a NULL?
>> So we thought about modifying the stored procedure by adding an extra parameter that will indicate which set of columns to return. <<
Since the correct form of a procedure is "<verb><object>", what would
you name your module? It returns multiple kinds of things, in
violation of cohesion. I call such things "Britney Spears, Automobiles
and Squid" procedures.
>> For modifying the stored procedure in order to get a variable name of columns returned and avoiding repeating code, we built 4 objects: the stored procedure being called, one table function and 2 views ... dynamic SQL statement, where it queries the table function and the views, depending which set is required.<<
When we violate the basics of SE, the code gets ugly and
un-maintainable pretty fast!
>> We would like to know what you think of this approach and if you know a simpler way of doing it. <<
Write one VIEW or SP for each clearly defined result set. Do not try
to cram everything into one SQL module. And please read some Yourdon,
DeMarco, Myers, etc. and the basics of structured programming. Your
SQL is a nightmare not because of a tricky SQL problem, but because you
do not know how to design a correct program.
Navigation:
[Reply to this message]
|