I inherited a database which contains sprocs (and a lot of other BL)

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…

This entry was posted in ASP.NET, Chatter, Data. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • http://www.colinblair.com Colin Blair

    Rule 1: If you have a multi-step, set based operation then you can use a stored procedure.
    Rule 2: If you don’t have a multi-step, set based operation then don’t use a stored procedure.
    Rule 3: If you need to use a cursor, you don’t have a set based operation. Refer to Rule 2.

    I am not sure why people can’t understand those simple rules. The “can” in rule 1 is very important.

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    @John

    The proc with the original’s was copied straight form the VS wizard. It’s their default way of handling optimistic locking. Does have quite an overhead indeed.

    Indeed the life of a consultant folllows bizare paths. On one hand I envy those who are given the opportunity to set up their projects right. On the other hand it’s quite interesting what you find in everyday life. Every square inch taken is worth the effort.

    @Adam
    Thanks a lot for your clear and very informative comment. So I’ve described another way to misuse a sproc :)

  • http://www.datamanipulation.net Adam Machanic

    Stored procedures are not — and never can be — “parameterized views”. A view in an SQL database can be treated the same as a table in virtually every context:

    SELECT *
    FROM Tbl

    vs.

    SELECT *
    FROM View

    The same is not ever true for a stored procedure:

    SELECT *
    FROM YourStoredProcedure — This does not work

    Stored procedures expose no explicit output contract, and this is the key differentiator (and reason that the above does not work). Thanks to conditional branching, dynamic SQL, etc, a stored procedure can (if you code it that way) output vastly different results based on different input parameters. Note, that’s an extremely poor (and dangerous) coding habit to get into — but the fact remains that it is impossible to verify the output of a stored procedure for a given set of input parameters without running it.

    This behavior is in stark contrast to a view, which provides a couple of means of verification:

    A) The output columns/data types can be verified, and bound to, before actually querying the view

    B) A view can be “schema bound”, meaning that the underlying base tables (or other views) which the view is based on cannot be changed, schema-wise, unless the view is dropped.

    This second feature provides somewhat of a guarantee about the correctness of the view, and brings to SQL Server a certain sense of “early binding” which is not present in stored procedures. A stored procedure late binds to the underlying schema, which is why you can create the following proc:

    CREATE PROC XYZ
    AS
    SELECT *
    FROM thisTableDoesNotExist
    GO

    All of this is the reason that as of SQL Server 2000 we have table-valued UDFs. A table-valued UDF is parameterized, has an explicit output contract, and can be schema bound. If you are truly looking for parameterized views and not stored procedures (which also have their place, of course), I think you can do a lot better by shifting a lot of this logic into UDFs.

  • http://codebetter.com/blogs/john.papa johnpapa

    Views and triggers have their place, but we often see them overused. i feel your pain. :) I am a consultant too and have seen many applications I wish I could have changed but time, the client, or someother reason prohibited it.

    As far as procs go … I can go either way on this issue depending on the situation. I noticed the proc you showed passes original values in the WHERE clause … it would be great (if there was time) to replace that with a ROWVERSION / TIMESTAMP column. Much easier to deal with. I like procs for CRUD … but I agree the iteration logic (like your other proc example) should go in .NET code.

    Ah the life of a consultant :)