Last week another rant on stored procs in databases passed by. A lot on it has been said over and over again but still I would like to add my 2 eurocents. Recently I inherited a database which contained a lot of sprocs and other coded logic. So I was forced into some more real world experience. The thing I would like to state in advance is: "It's not about stored procedures but about T-SQL in general". This is not about TDD either but, for different reasons, I will end up thinking not to positively about (some) sprocs either.
First of al I have to mention that neither time, resources, budget or culture of the customer had any room for a good thorough re-grounding of the project towards OR-mapping or TDD. The project was a classical 2-tier Delphi SQL server 2000 CS application which we had to turn into an ASP.NET application. We were a two person team: me and an application manager with a historical (and good!) working knowledge of T-SQL. An OR-mapper was beyond the horizon; when it came test-driven the main thing we missed mostly in the end was regression testing: "are all event-handlers still attached to the button's", "do the database and the code still match" or even worse "are we talking to the right database". A little more on that later.
The first concern was to get the data separated from the (ASP.NET) user interface. A lot of the documentation was in the form of (Delphi) source code, the separation of the layers was not always too good. As a start I wrapped the database in an assembly which defined a limited number of xsd schemas whose definitions were steered by the need of the application and not the physical structure of the database. The data was sent in and out of the assembly by passing datasets. Inside the assembly I could play with my SQL and the sql found in the database.
When battling the database 3 forms of T-SQL coded logic were found:
Views
A view is a selection of rows and columns from one ore more database tables. The views defined provided a clear way to start working with the database. They define a set of data. SQL is very good at this, before there was linq there is nothing which beats a SELECT statement with a couple of JOINS. It's a .net 1.1 project so linq is out of the question. To write a view I prefer using a visual tool like sql server's query builder. A picture beats a thousand words and clicking the checkboxes of a column is faster and less error prone than writing out the full column name.
But views have one enormous disadvantage: they do not support parameters. Our system has a lot of user defined selections; it's not unusual for a user to do a selection on two or three columns out of a list of eight. These are not ad-hoq queries; it's a basic requirement. This would require eight parameters to the view (which is not possible) and a check for a null value on every parameter (which would add needless complexity to the query. In comes the dynamically generated sql. To get best of all worlds I ended up using sqlAdapters to get a nice design time experience and generate the dynamic sql from code which uses the adapters command text and adds parameters where required. Something like this, which operates on a dataAdapter named StudieOnderdelenSelectie
StringBuilder bc = new StringBuilder();
if (selParms.opleiding > 0)
{
StudieOnderdelenSelectie.SelectCommand.Parameters.Add("@Opleiding", selParms.opleiding);
bc.Append(string.Format(" AND idOpleiding = @Opleiding"));
}
if (DBoperations.checkStringInput(selParms.studieOnderdeel))
{
StudieOnderdelenSelectie.SelectCommand.Parameters.Add("@StudieOnderdeel", selParms.studieOnderdeel + "%");
bc.Append(string.Format(" AND (StudieOnderdeel LIKE @StudieOnderdeel)"));
}
if (selParms.jaargang > -1)
{
StudieOnderdelenSelectie.SelectCommand.Parameters.Add("@Jaargang", selParms.jaargang);
bc.Append(string.Format(" AND jaargang = @Jaargang"));
}
if (selParms.periode > -1)
{
StudieOnderdelenSelectie.SelectCommand.Parameters.Add("@Periode", selParms.periode);
bc.Append(string.Format(" AND periode = @Periode"));
}
if (selParms.idAfdeling > -1)
{
StudieOnderdelenSelectie.SelectCommand.Parameters.Add("@idAfdeling", selParms.idAfdeling);
bc.Append(string.Format(" AND idAfdeling = @idAfdeling"));
}
sortOrder = "StudieOnderdeel";
bc.Append(string.Format(" ORDER BY {0}", sortOrder));
StudieOnderdelenSelectie.SelectCommand.CommandText+= bc.ToString();
This code can be applied only once on a sqlAdapter. It works in a web based environment where the adapter is per definition recreated on every roundtrip. In a winforms app this is different. Another thing to watch is the the ORDER BY part. It always has to come last. If you set it at design time you'll have to parse the adapters sql text to find the right place to inject (pun not intended) your sql.
I'm not proud of this code but it provides a very workable scenario.
Stored procedures
The moment you start working with parameters in a sql server tool like the enterprise manager, query analyzer or the like you enter the world of stored procedures. In my view there a three kind of stored procs. One is a view with parameters. All the sql included does is construct a set of data to return. Again this is sql at its best. As the previous part should have made clear I don't like this functionality as part of the database. Give me a view or a table and my code will construct the parameters as desired.
The second kind of stored procs updates sets of data, things like
UPDATE RoosterActiviteitDocent SET idRoosterActiviteit = @idRoosterActiviteit, idPersoneel = @idPersoneel WHERE (idRoosterActiviteitDocent = @Original_idRoosterActiviteitDocent) AND (idPersoneel = @Original_idPersoneel) AND (idRoosterActiviteit = @Original_idRoosterActiviteit); SELECT idRoosterActiviteitDocent, idRoosterActiviteit, idPersoneel FROM RoosterActiviteitDocent WHERE (idRoosterActiviteitDocent = @idRoosterActiviteitDocent)
Again you can have this code as stored procs in your database but you can also generate the sql from code. Something your favorite OR-mapper or even VS can do it for you. The example above was built by the sqlAdapater wizard.
The third kind of stored procs is where sql imho shows a very ugly face. It's not sql which defines a set of data but starts traversing on its own. To take a snippet
....
open
IDAFDELING_CURS
fetch next from
IDAFDELING_CURS
into
@AIDAFDELING
while (@@fetch_status = 0)
begin
declare IDJRBDGAFD_CURS cursor
for select
IDJRBDGAFD,
BEDRAG
from
JRBDGAFD
where
IDCURSUSJAAR = @AIDCURSUSJAAROUD and
IDAFDELING = @AIDAFDELING;
open
IDJRBDGAFD_CURS
fetch next from
..
Here it's sql as a general programming language. Needless to say a language as C# (or vb.net, Delphi, etc) has far more and better programming and debugging possibilities. These stored procs are a point of pain in the application. They do quite important things but, like al code, contain bugs. Bugs which are very hard to find and fix. Some of the sprocs do take a lot of time to complete which is an extra problem in the new asp.net version of the application; the former windows CS app would just show an hourglass for quite some time. But, provided you don't take action, a web request has several places to time out.
Triggers
Triggers go off when data in the database is changed. Inside a trigger you can write almost any sql you desire. Included all of the horrors I mentioned in the stored proc part. And perhaps even worse. For instance it is possible to start a new transaction inside a trigger. What will happen if the transaction inside the trigger commits but the transaction which fired the trigger fails? This is without a doubt hidden somewhere inside the docs but I even don't want to think about it. Our system does not do that much inside its triggers. No traceable problems yet. Thank goodness as triggers are even harder to debug.
My conclusions
As the story should have made clear I'm not that happy with any coded logic in our database at all; most of it can be done far better in C# from a layer in the application. In his posts Eric has mentioned some strong points in favor of putting it in the database nevertheless. This is my view on these
- Maintainability. With a DB server this is an issue in general. The tools to write, debug and organize code in a database completely pale in comparison with Visual Studio. The less code in the database, the better. And I am a very happy user of RedGate's sql Compare to compare and update anything in the database.
- Security. SqlServer has a very fine grained role based security system which can be applied down to the column level. Why hide that in a view? What if some admin creates another view without the restriction? That's not possible when the security was set on the table itself.
- A division of responsibilities. Just like Jeremy I could not agree less on this. Involving the DBA and all other IT staff as early and much as possible only helps to get your project up and running. We may have some problems with database coded logic but these completely pale by the problems we're facing with the IT pro staff. These problems boil down to a lack of involvement on their side; they try to handle the project (which spans multiple servers in the network) as a help-desk ticket and refuse any involvement deeper than the "next, next, finish" level. Even checking whether the app connects to the right database can be a problem... My co-worker, who has to handle the far more difficult database stuff, is very involved and dedicated. In my view that has saved the project from becoming a nightmare. After all it's the man not the technology.
My future
For new functionality we needed some complex data manipulation which included things like splitting a column into an arbitrary number of new columns. The classical approach would have been to write a complex stored proc which would create a temporary table in the database and fiddle with that. Instead I wrote (visually designed :)) a straightforward SQL select statement to fill a dataset. And handled all the column and row manipulation in plain C# working with the dataset.
Such a relief...