Well sometimes we have good dreams, as sometimes we have good procedures, but most of the time it is just a dream or even worst: a nightmare.
Let’s put like this: even water could kill in a excessive amount, imagine procedures. That is what I am facing in the current project, a land with a lot of procedures: some of them are like the Cheshire Cat others like the Queen of Hearts, but for sure they are not from our world.
Why are they not from here? You my asked me: well can you version stored procedures? Can you unit test or debug stored procedures? Is there a coding pattern? Can you use common development concepts, fair simple ones like: functions, code split, reuse? Probably the answer to most of these question is: sorry you cannot do it!
Ok folks, before you start blaming me about performance issues: I agree, there is a right place to everyone, even to the stored procedures. But before the end let’s take a look to some code around my project, look how the dependency to the stored procedure recreates some filthy ORCS:
create procedure PP_TODAY
as
begin
select today=convert(char(10),getdate(),103)
end
create procedure PP_HOUR
as
begin
select hour=convert(varchar,getdate(),103) + " " + convert(varchar,getdate(),108)
end
How can we afford this? And do you know what: these procedures are used as part of other procedures in a batch mode. Yes that is true: batch! The files are read and stored in temporary tables and after that a procedure reads each line and them split it, put it into another table and them make a huge processing stack. Let’s face it: we are expending time and resource from the database machine to process something that could be done in another place, look it is even worst: we probably will be running against some production code, slowing down our user and requiring a more and more powerful machine (and at the end blaming the DBMS that we use).
Now I will ask you: is it right? Is it something that Alice would do in the wonderland?
