The Worst Possible Way to use a Stored Procedure

Rant time…

Repeat after me please,

I will not put any business logic into a stored procedure.
I will not put any business logic into a stored procedure.
I will not put any business logic into a stored procedure.
I will not put any business logic into a stored procedure.
I will not put any business logic into a stored procedure.

Okay, if your business logic can be expressed easiest through a declarative SQL WHERE clause, a SPROC can be a cool way to go. Set-based logic is almost always simpler to do with SQL than mucking through with procedural C#, but that’s a different rant for another day, and I’m definitely talking about procedural code within sproc’s here.

Question: How do I know if I might be doing something in a sproc that I shouldn’t be doing? Answer: If there is *anything* other than CRUD in your sproc (IF/ELSE/ENDIF, LOOP/END LOOP, etc.).

The all time dumbest thing to do is to split your business logic between a SPROC and the consuming middle tier code. Twice now in the past 12 months I’ve bumped into cases where business logic is performed inside a sproc, then interpreted by middle tier C# or VB6 code. Splitting the logic into multiple spots like this makes a system harder to understand. It also makes a system brittle to change because some logic is duplicated in both places. A change in the sproc or C# can, and usually does, break the other. This is a case of the Don’t Repeat Yourself principle the Pragmatic Programmer guys talk about.

I had an interesting experience as a consultant one time. I was fresh into the project working with some legacy C# code (yes, there is already legacy .NET code). I had just stumbled on the fact that most of the business logic was really in T-SQL procedures. In many cases, the C# code undid some of the T-SQL transformations to filter the results further. The client architect was giving us a bit of a tongue-lashing (“you consultants better not write crappy code, and there better be tests for everything you write”) while I was looking at a 5,000 line stored procedure, with his name all over the comments, trying to decide if some erroneous data was coming out of the database or being transformed in the C# code. This particular system is the subject of a case study/testimonial on MSDN as an example of all the wonderful things about .NET;)

“Thank you for listening, I feel better now.” — Roy Moore

About Jeremy Miller

Jeremy is the Chief Software Architect at Dovetail Software, the coolest ISV in Austin. Jeremy began his IT career writing "Shadow IT" applications to automate his engineering documentation, then wandered into software development because it looked like more fun. Jeremy is the author of the open source StructureMap tool for Dependency Injection with .Net, StoryTeller for supercharged acceptance testing in .Net, and one of the principal developers behind FubuMVC. Jeremy's thoughts on all things software can be found at The Shade Tree Developer at http://codebetter.com/jeremymiller.
This entry was posted in Database and Persistence, Ranting. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • http://www.ledgersmb.org/ Chris Travers

    I can’t speak for the OP but we have better test coverage of our stored procedures in LedgerSMB than we do over the rest of the code.

  • http://www.ledgersmb.org/ Chris Travers

    5000 lines is a bit excessive for a stored procedure. Usually in the 200-500 line range the stored procedure is either necessary and better than the alternative or it is absolute hell to work with if poorly managed. This is the way it goes.

    Generally there are two fundamental problems I see in stored procedures (I am pro-stored-procedure in cases where queries need to be re-used btw, but recognize the need to have service locator patterns etc to avoid brittleness) are:

    1. Doing non-transactional business logic. Sending email is the most common.

    2. If you have large numbers of simpler queries, chained together, stored procedures are a pain to debug. It is *far* better to have a few large queries and some simple minor support logic (If/then/else, for/loop) which is there only to support the main query, than it is to have a lot of complex chaining of queries.

  • Anonymous

    what kind of test coverage do you have for all that logic?

  • Prez

    Hi,
    i am working with one application which has almost 4000 lines of code in sproc…and there are many sproc like that…
    We are continuously adding requirement in tht system…
    But complication here is..tht…each stored proc uses almost 5-10 tables many times…and some of tables have millions of records…we make multiple joins in such tables many times…
    Now if we think to seperate our business logic in to another Business layer then i have doubt that system will perform as of now….

    Regards,
    Prez.

  • OMG

    Also – have you got any experience how to find what particular prepared statement started to slow down application that running against SQL Server 2000? I had – I spent more then half a day to find the real script that hidden under handle of temporary SP that wraps real query. Just try to catch these calls in profiler – you will be happy. I can translate this trace in plain English like this: the SP with handle 15 runs 300 seconds. Very informative and useful – but you can test it by yourself if you don’t believe me. BTW it is not only “mine” problem: http://bytes.com/topic/sql-server/answers/497441-finding-prepared-statement-sql Please, pay attention to this words in the message ”The statement is prepared before I start the profiler and the client
    keeps that connection – so I can’t see its preparation without restarting the server (it’s a web application).” Can you imagine how to restart, say ebay.com? (BTW AFAIK ebay is using SQL Server as database server)

    PS Sorry – I’m not spaming – only aproximately every third message appears in several minutes after send

  • WhyIcannotSend

    As you can see – second query logic is absolutely the same like the first one. The difference is one – second one is acting as prepared statement (be careful – I did not say that it is prepared statement – I said that this statement will act the same way as prepared statement. FYI – I know in details how works optimizer in SQL Server). Last time when I’ve seen the developer with the attitude like yours (“I will not put any business logic into a stored procedure”) and who used only prepared statements as you – he came to me with exactly this queries and request to put this query in SP. For my question where is a problem from his point of view he answered: “it is JDBC/ODBC driver error. Through SP this would be faster”. I did SP for him and show that there is no performance improvement at all using SP. But in QA he still able to run the query in 0 milliseconds. He was amazed and had no answer. Now is your shot. Any idea? I know the answer why the timing is so different. I can proof it and explain it. (this link can give you some light in the dark but it has no full answer: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/12612/Prepared-Statement-vs-Stored-Procedure)

  • WhyIcannotSendMessages1

    As you can see – second query logic is absolutely the same like the first one. The difference is one – second one is acting as prepared statement (be careful – I did not say that it is prepared statement – I said that this statement will act the same way as prepared statement. FYI – I know in details how works optimizer in SQL Server). Last time when I’ve seen the developer with the attitude like yours (“I will not put any business logic into a stored procedure”) and who used only prepared statements as you – he came to me with exactly this queries and request to put this query in SP. For my question where is a problem from his point of view he answered: “it is JDBC/ODBC driver error. Through SP this would be faster”. I did SP for him and show that there is no performance improvement at all using SP. But in QA he still able to run the query in 0 milliseconds. He was amazed and had no answer. Now is your shot. Any idea? I know the answer why the timing is so different. I can proof it and explain it. (this link can give you some light in the dark but it has no full answer: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/12612/Prepared-Statement-vs-Stored-Procedure)

  • WhyIcannotSendMessages

    > I use 100% parameterized queries

    This exactly what I meant. Thank you for good example. Can you give ANY IDEA why the query like this

    SELECT id FROM table WHERE Active = 1

    will run zero milliseconds but this query

    DECLARE @Active_val int
    SET @Active_val = 1
    SELECT id FROM table WHERE Active = @Active_val

    will run more then 6 minutes? It is real example and real timing. Table is completely the same and untouched between queries, server is the same, no other application or users on the server, any SQL Server cache is flushed before each call.

  • gennadi

    > I use 100% parameterized queries

    This exactly what I meant. Thank you for good example. Can you give ANY IDEA why the query like this

    SELECT id FROM table WHERE Active = 1

    will run zero milliseconds but this query

    DECLARE @Active_val int
    SET @Active_val = 1
    SELECT id FROM table WHERE Active = @Active_val

    will run more then 6 minutes? It is real example and real timing. Table is completely the same and untouched between queries, server is the same, no other application or users on the server, any SQL Server cache is flushed before each call. As you can see – second query logic is absolutely the same like the first one. The difference is one – second one is acting as prepared statement (be careful – I did not say that it is prepared statement – I said that this statement will act the same way as prepared statement. FYI – I know in details how works optimizer in SQL Server). Last time when I’ve seen the developer with the attitude like yours (“I will not put any business logic into a stored procedure”) and who used only prepared statements as you – he came to me with exactly this queries and request to put this query in SP. For my question where is a problem from his point of view he answered: “it is JDBC/ODBC driver error. Through SP this would be faster”. I did SP for him and show that there is no performance improvement at all using SP. But in QA he still able to run the query in 0 milliseconds. He was amazed and had no answer. Now is your shot. Any idea? I know the answer why the timing is so different. I can proof it and explain it. (this link can give you some light in the dark but it has no full answer: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/12612/Prepared-Statement-vs-Stored-Procedure)

    Also – have you got any experience how to find what particular prepared statement started to slow down application that running against SQL Server 2000? I had – I spent more then half a day to find the real script that hidden under handle of temporary SP that wraps real query. Just try to catch these calls in profiler – you will be happy. I can translate this trace in plain English like this: the SP with handle 15 runs 300 seconds. Very informative and useful – but you can test it by yourself if you don’t believe me. BTW it is not only “mine” problem: http://bytes.com/topic/sql-server/answers/497441-finding-prepared-statement-sql Please, pay attention to this words in the message ”The statement is prepared before I start the profiler and the client
    keeps that connection – so I can’t see its preparation without restarting the server (it’s a web application).” Can you imagine how to restart, say ebay.com? (BTW AFAIK ebay is using SQL Server as database server)

  • Gennadi

    > I use 100% parameterized queries

    This exactly what I meant. Thank you for good example. Can you give ANY IDEA why the query like this

    SELECT id FROM table WHERE Active = 1

    will run zero milliseconds but this query

    DECLARE @Active_val int
    SET @Active_val = 1
    SELECT id FROM table WHERE Active = @Active_val

    will run more then 6 minutes? It is real example and real timing. Table is completely the same and untouched between queries, server is the same, no other application or users on the server, any SQL Server cache is flushed before each call. As you can see – second query logic is absolutely the same like the first one. The difference is one – second one is acting as prepared statement (be careful – I did not say that it is prepared statement – I said that this statement will act the same way as prepared statement. FYI – I know in details how works optimizer in SQL Server). Last time when I’ve seen the developer with the attitude like yours (“I will not put any business logic into a stored procedure”) and who used only prepared statements as you – he came to me with exactly this queries and request to put this query in SP. For my question where is a problem from his point of view he answered: “it is JDBC/ODBC driver error. Through SP this would be faster”. I did SP for him and show that there is no performance improvement at all using SP. But in QA he still able to run the query in 0 milliseconds. He was amazed and had no answer. Now is your shot. Any idea? I know the answer why the timing is so different. I can proof it and explain it. (this link can give you some light in the dark but it has no full answer: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/12612/Prepared-Statement-vs-Stored-Procedure)

    Also – have you got any experience how to find what particular prepared statement started to slow down application that running against SQL Server 2000? I had – I spent more then half a day to find the real script that hidden under handle of temporary SP that wraps real query. Just try to catch these calls in profiler – you will be happy. I can translate this trace in plain English like this: the SP with handle 15 runs 300 seconds. Very informative and useful – but you can test it by yourself if you don’t believe me. BTW it is not only “mine” problem: http://bytes.com/topic/sql-server/answers/497441-finding-prepared-statement-sql Please, pay attention to this words in the message ”The statement is prepared before I start the profiler and the client
    keeps that connection – so I can’t see its preparation without restarting the server (it’s a web application).” Can you imagine how to restart, say ebay.com? (BTW AFAIK ebay is using SQL Server as database server)

  • AAA

    Rather then teach me with manner you better answer the questions. It is ordinary questions that rise every day/month/quarter if you support “large systems”. BTW what is the penalty for breaching SLA written in contract? Just number of zeros in penalty for one hour of outage above SLA and percentage of up time according SLA – this will be a good answer how large the systems are. Basically I was talking about 99.6% per month (24×7 uptime) and six zeros number for one hour in my case.

    > They’re usually used to prevent “those stupid guys” from doing something “stupid” rather than adding any value.

    Have you got any idea why I wrote about 70% RAM consumed for execution plans? What do you think – how this correlate with your phrase I quoted? There is a direct connection between them. But if you think that you can sacrifice 70% of RAM to nothing having in database tables that keeps millions of records – probably I’ve asked stupid question.

    > So I agree with you there, don’t use embedded SQL.

    Do you think it somehow change the meaning of my question:

    How to fix it in case of embedded sql if hundreds users must have 24×7 access to the system?

    if I will change “embedded” to “prepared”?

    Again – just imagine that your application is middle tier. Hundreds of users are connected to it 24 hours per day. 1 hour of outage cost you six zeros in penalty. And you have to fix one (only one!!!) query in this application where this query is hardcoded (embedded or dynamic – doesn’t matter) in your EXE/DLL/ets. This means you have to disconnect hundreds users, stop the middle tier service, replace EXE/DLL for new version, start service, check that application is working as expected. And run the same  procedure to rollback if upgrade is failed. Just compare this with one ALTER PROCEDURE clause (say 10-20 milliseconds) for fix upgrade and another ALTER PROCEDURE (again 10-20 milliseconds) for rollback. All users still connected and likely even don’t mention that they are working with new version of SP. Again – keep in mind that the business we are talking about loosing physically, say, 10 grands per minute. BTW it is ordinary number, say, for GM assembly belt line. Can you compare “cost” of “your” solution and “mine”?

  • Gennadi1

    Rather then teach me with manner you better answer the questions. It is ordinary questions that rise every day/month/quarter if you support “large systems”. BTW what is the penalty for breaching SLA written in contract? Just number of zeros in penalty for one hour of outage above SLA and percentage of up time according SLA – this will be a good answer how large the systems are. Basically I was talking about 99.6% per month (24×7 uptime) and six zeros number for one hour in my case.

    > They’re usually used to prevent “those stupid guys” from doing something “stupid” rather than adding any value.

    Have you got any idea why I wrote about 70% RAM consumed for execution plans? What do you think – how this correlate with your phrase I quoted? There is a direct connection between them. But if you think that you can sacrifice 70% of RAM to nothing having in database tables that keeps millions of records – probably I’ve asked stupid question.

    > So I agree with you there, don’t use embedded SQL.

    Do you think it somehow change the meaning of my question:

    How to fix it in case of embedded sql if hundreds users must have 24×7 access to the system?

    if I will change “embedded” to “prepared”?

    Again – just imagine that your application is middle tier. Hundreds of users are connected to it 24 hours per day. 1 hour of outage cost you six zeros in penalty. And you have to fix one (only one!!!) query in this application where this query is hardcoded (embedded or dynamic – doesn’t matter) in your EXE/DLL/ets. This means you have to disconnect hundreds users, stop the middle tier service, replace EXE/DLL for new version, start service, check that application is working as expected. And run the same  procedure to rollback if upgrade is failed. Just compare this with one ALTER PROCEDURE clause (say 10-20 milliseconds) for fix upgrade and another ALTER PROCEDURE (again 10-20 milliseconds) for rollback. All users still connected and likely even don’t mention that they are working with new version of SP. Again – keep in mind that the business we are talking about loosing physically, say, 10 grands per minute. BTW it is ordinary number, say, for GM assembly belt line. Can you compare “cost” of “your” solution and “mine”?

  • Gennadi

    Rather then teach me with manner you better answer the questions. It is ordinary questions that rise every day/month/quarter if you support “large systems”. BTW what is the penalty for breaching SLA written in contract? Just number of zeros in penalty for one hour of outage above SLA and percentage of up time according SLA – this will be a good answer how large the systems are. Basically I was talking about 99.6% per month (24×7 uptime) and six zeros number for one hour in my case.

    > They’re usually used to prevent “those stupid guys” from doing something “stupid” rather than adding any value.

    Have you got any idea why I wrote about 70% RAM consumed for execution plans? What do you think – how this correlate with your phrase I quoted? There is a direct connection between them. But if you think that you can sacrifice 70% of RAM to nothing having in database tables that keeps millions of records – probably I’ve asked stupid question.

    > So I agree with you there, don’t use embedded SQL.

    Do you think it somehow change the meaning of my question:

    How to fix it in case of embedded sql if hundreds users must have 24×7 access to the system?

    if I will change “embedded” to “prepared”?

    Again – just imagine that your application is middle tier. Hundreds of users are connected to it 24 hours per day. 1 hour of outage cost you six zeros in penalty. And you have to fix one (only one!!!) query in this application where this query is hardcoded (embedded or dynamic – doesn’t matter) in your EXE/DLL/ets. This means you have to disconnect hundreds users, stop the middle tier service, replace EXE/DLL for new version, start service, check that application is working as expected. And run the same  procedure to rollback if upgrade is failed. Just compare this with one ALTER PROCEDURE clause (say 10-20 milliseconds) for fix upgrade and another ALTER PROCEDURE (again 10-20 milliseconds) for rollback. All users still connected and likely even don’t mention that they are working with new version of SP. Again – keep in mind that the business we are talking about loosing physically, say, 10 grands per minute. BTW it is ordinary number, say, for GM assembly belt line. Can you compare “cost” of “your” solution and “mine”?

  • Gennadi

    Rather then teach me with manner you better answer the questions. It is ordinary questions that rise every day/month/quarter if you support “large systems”. BTW what is the penalty for breaching SLA written in contract? Just number of zeros in penalty for one hour of outage above SLA and percentage of up time according SLA – this will be a good answer how large the systems are. Basically I was talking about 99.6% per month (24×7 uptime) and six zeros number for one hour in my case.

    > They’re usually used to prevent “those stupid guys” from doing something “stupid” rather than adding any value.

    Have you got any idea why I wrote about 70% RAM consumed for execution plans? What do you think – how this correlate with your phrase I quoted? There is a direct connection between them. But if you think that you can sacrifice 70% of RAM to nothing having in database tables that keeps millions of records – probably I’ve asked stupid question.

    > So I agree with you there, don’t use embedded SQL.

    Do you think it somehow change the meaning of my question:

    How to fix it in case of embedded sql if hundreds users must have 24×7 access to the system?

    if I will change “embedded” to “prepared”?

    Again – just imagine that your application is middle tier. Hundreds of users are connected to it 24 hours per day. 1 hour of outage cost you six zeros in penalty. And you have to fix one (only one!!!) query in this application where this query is hardcoded (embedded or dynamic – doesn’t matter) in your EXE/DLL/ets. This means you have to disconnect hundreds users, stop the middle tier service, replace EXE/DLL for new version, start service, check that application is working as expected. And run the same  procedure to rollback if upgrade is failed. Just compare this with one ALTER PROCEDURE clause (say 10-20 milliseconds) for fix upgrade and another ALTER PROCEDURE (again 10-20 milliseconds) for rollback. All users still connected and likely even don’t mention that they are working with new version of SP. Again – keep in mind that the business we are talking about loosing physically, say, 10 grands per minute. BTW it is ordinary number, say, for GM assembly belt line. Can you compare “cost” of “your” solution and “mine”?

    > I use 100% parameterized queries

    This exactly what I meant. Thank you for good example. Can you give ANY IDEA why the query like this

    SELECT id FROM table WHERE Active = 1

    will run zero milliseconds but this query

    DECLARE @Active_val int
    SET @Active_val = 1
    SELECT id FROM table WHERE Active = @Active_val

    will run more then 6 minutes? It is real example and real timing. Table is completely the same and untouched between queries, server is the same, no other application or users on the server, any SQL Server cache is flushed before each call. As you can see – second query logic is absolutely the same like the first one. The difference is one – second one is acting as prepared statement (be careful – I did not say that it is prepared statement – I said that this statement will act the same way as prepared statement. FYI – I know in details how works optimizer in SQL Server). Last time when I’ve seen the developer with the attitude like yours (“I will not put any business logic into a stored procedure”) and who used only prepared statements as you – he came to me with exactly this queries and request to put this query in SP. For my question where is a problem from his point of view he answered: “it is JDBC/ODBC driver error. Through SP this would be faster”. I did SP for him and show that there is no performance improvement at all using SP. But in QA he still able to run the query in 0 milliseconds. He was amazed and had no answer. Now is your shot. Any idea? I know the answer why the timing is so different. I can proof it and explain it. (this link can give you some light in the dark but it has no full answer: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/12612/Prepared-Statement-vs-Stored-Procedure)

    Also – have you got any experience how to find what particular prepared statement started to slow down application that running against SQL Server 2000? I had – I spent more then half a day to find the real script that hidden under handle of temporary SP that wraps real query. Just try to catch these calls in profiler – you will be happy. I can translate this trace in plain English like this: the SP with handle 15 runs 300 seconds. Very informative and useful – but you can test it by yourself if you don’t believe me. BTW it is not only “mine” problem: http://bytes.com/topic/sql-server/answers/497441-finding-prepared-statement-sql Please, pay attention to this words in the message ”The statement is prepared before I start the profiler and the client
    keeps that connection – so I can’t see its preparation without restarting the server (it’s a web application).” Can you imagine how to restart, say ebay.com? (FYI – AFAIK ebay is using SQL Server as database server)

  • http://chadmyers.lostechies.com Chad Myers

    @Gennadi:

    It’s bad form to just assume the other guy is an idiot with no experience. Perhaps you should challenge your preconceived notions and false assumptions instead of insulting other people.

    It is precisely BECAUSE I have maintained such large systems that I don’t use stored procs. In my experience, except in a very few circumstances, stored procs represent a failure at some point either during development, organization, team cooperation, etc. They’re usually used to prevent “those stupid guys” from doing something “stupid” rather than adding any value. Whether it be insecure DBAs or over-bearing managers wishing to keep members of his team separate or distrusting teams working on different projects, but sharing a database, etc.

    Integration of applications through the database is the least-common-denominator and is generally the worst way to do it. There are much better ways.

    RE: Embedded SQL – Embedded SQL is evil. Worse than Sprocs, I think. So I agree with you there, don’t use embedded SQL.

    RE: that-article I haven’t read that one in particular, but I’ve heard the argument before. Once again, I’m not doing dynamic SQL like that, and, at any rate, I use 100% parameterized queries that are consistently generated from an ORM so the procedure cache argument is out the window.

    I suggest you do a little more investigation and try doing some apps my way before just dismissing it. I’ve done them your way and the better way and have drawn my conclusions. You have dismissed my arguments without having even considered my way and then threw in a red herring (i.e. not embedded/dynamic SQL).

    -Chad

  • Gennadi

    It looks like you never support any system you wrote. It is very sad. You will damn yourself

    Can you please explain how you manage capacity planning for the application for next, say 5 years and growing data 10 times?

    How you monitor that hardware is sufficient to handle increasing of user activity? How to find when system will require hardware upgrade? And for how long this upgrade will last to next upgrade? I’m asking not only about HDD capacity – I’m about processor(s) capacity and RAM.

    Or how you will deal with situation when your application that run on middle tier started depredates in performance because set of data in tables changed and some query should revised?

    How to fix it in case of embedded sql if hundreds users must have 24×7 access to the system?

    With SP I will localize the particular problem SP in 5 minutes and after solution would be found – upgrade of application will take several milliseconds without any interruption of users activity – just apply ALTER PROCEDURE.

    Last time when we had the situation like this it took 3 (three) days for preparation to stop application server (after fix been found – that also took couple of days, application recompiled and so one) and several hours of outage for all users. Of course this happens in most busy for business time. Business owners were absolutely happy – screaming users for week time, lost of money for business interruption. It was real pleasure.

    BTW – have you red this article? http://www.sommarskog.se/dynamic_sql.html What do you think about memory usage for execution plans? Especially in situation when these plans consume 70% of RAM and used only ones? (if you don’t know – two statements like “id=1” and “id= 1” will be recognized as different and compiled separately)

  • Chad Myers

    @Roman: It’s not that SPs aren’t useful, it’s just that the end up causing a lot more problems than they solve and they end up being a much more difficult and problematic way of doing things.

    There are a few limited use cases where SPs are the best way of accomplishing a particular task, but none of these cases involve putting business logic in the SPs.

    Use of SPs for business logic usually reflect a larger problem in the organization. The example you described is clearly the “integrate through the database” anti-pattern which is fraught with many problems. SPs are a way of addressing some of these problems, but they are an inferior way of doing it because SPs themselves cause other problems.

  • Roman

    One (a few) more thing(s):

    Your comment:

    “A.) I think that more than one application or service accessing the same database is moronic. Very common in the average enterprise, but still stupid as the day is long. ”

    You may live in a world where you are allowed to specify infrastructure, design, technologies, etc. all by yourself, but most of us work for someone else .

    I have a client that has a central database of students, where disperate systems access student data directly from the central admin system. (library, billing, purchasing, school admin, etc) The interface to provide, for example, “StudentsByCourse” is the SAME for all of these applications. This represents a common API that all these applications can use.

    You will probably argue that this layer should be exposed using another technology, i.e. a webservice or similar, but why? There is no need to do so. I can give each system the rights to retrieve the data as necessary from a common interface.

    I do understand that if, for example, the library app needed DIFFERENT fields than say, billing, it could be beneficial to have a layer in between, however it makes no logical difference to create a new SP or to add a function to a class to fit this custom need. In fact, adding the SP is easier.

    All that said, I do enjoy your blog, and it is good to read arguments for/against topics such as this. I would be interested in seeing/trying some of the SQL generation frameworks mentioned (just to see how the “other” side does it and to keep an open mind).

    Just one last question – did you have a bad experience with SPs as a small child? I can understand being forcefully “for” something, but so forcefully against an obviously useful (and, I admit, sometimes misused) functionality? I don’t understand.

    Cheers. R.

  • Roman

    Jeremy,
    Why would you think things would be more maintainable in a scripting language? We do version control for DB structure, schema, including SPs for 3-5 DBAs across several databases on a daily basis.

    There is absolutely no problem with this.

    As far as automated testing goes, that’s a very subjective avenue depending on how/what you test. We can (and do) easily write tests to iterate datasets, check for correct execution, return, etc. that test all layers, including SPs. We have no issues with testing for this scenario.

    You say yourself that you do not write stored procedures. I guess since your experience is very limited in this respect, then your (a bit too vehement) opinion on the subject should be taken, as they say, with a grain of salt.

  • http://codebetter.com/members/jmiller/default.aspx Jeremy D. Miller

    Ok Roman,

    Let’s try this one:

    A.) I think that more than one application or service accessing the same database is moronic. Very common in the average enterprise, but still stupid as the day is long.

    B.) I’ve written an absurd amount of SQL and even stored procedures in my career. I’m not particularly afraid of a relational database.

    C.) What I most definitely know is that I can write much more maintainable business logic in a real programming language like C#/Java/Ruby/Python/Scala etc. — especially when I can back that logic up with a good set of automated unit tests. A language like T-SQL or PL/SQL just results in a procedural mess and the database environment is miserable from a feedback and automation standpoint.

    D.) For your information, my team has a very large application that’s performing perfectly well with zero stored procedures, only a bare handful of lines of sql embedded in the code, but a pretty good suite of automated tests that run relatively quickly.

  • Roman

    What are you all talking about? How can you NOT have business logic in the DB if you have disperate systems connecting in a SOA environment? if your DB is serving data to more than exactly 1 application, how is it that you can purport to maintain data integrity and understandable logic outside of the database?

    This comment:

    “The motivation is usually one of fear and mistrust of developers. The solution (dumping into the black whole of the database) is the wrong solution and only treating the symptoms (incompetent developers and fearful DBAs).”

    That is just priceless. Fear? YOU are calling it the “Black Hole of the Database” Who’s the one afraid of SQL here?

    If you’re writing one-offs or throwing together a little blog or online shop, I guess your slow, SQL-laden scripting layer is OK, but not if you really need to make something work in an enterprise environment where homogeneous, service oriented access over a common interface is called for.

  • mendicant

    I’ll go on that “no hire” list too. I can’t imagine what it must be like for you having worked 32 years with such poor and untrustworthy developers.

  • Chad Myers

    @Andy: Put me on that list, too. It’s quite possibly the highest compliment you could pay me and Jeremy and most of the people reading this blog.

    Putting critical business logic in a black hole of no version control, no change control, and no way of doing automated repeatable testing is quite frankly the dumbest thing you can do.

    The motivation is usually one of fear and mistrust of developers. The solution (dumping into the black whole of the database) is the wrong solution and only treating the symptoms (incompetent developers and fearful DBAs).

  • Andy

    Remind me to put you on the ” do not hire list”… as a DBA , in ORACLE, and MSSQL, BL in the RDBMS is mandentory as languages used to interface with the RDBMS can be anything… and I don’t like cleaning up your mess.

    and yes, DBA since 1988, so got some experience…

  • http://www.nitinkatkam.com Nitin Reddy Katkam

    On my first job (and my first software project at a software development firm), I had a project manager on the project who insisted that I write the stored procedures, being the DBA on the project. The client shared his opinion.

    The development team, headed by a lead developer, kept pushing business logic my way, into the stored procedures. I stepped up and said, “This isn’t how code was meant to be written.” The project manager called me away and said, “Look, you can write SQL. They can’t. Provide a simple enough interface with which they can accomplish all they need to with a single database call.”

  • Ivan

    And finally, I’m completely confused… I’m looking for information about stored procedures, whether to use them or not… the more I read, the last I know…

    And the worst thing is that I have to explain to my development team the reasons of my decisions. But there’s no simple choose.

  • RogerBlake

    Business Logic should not be handled applicatively (API code, stored procedure) but declaratively through the use of constraints. TRying to implement business rules applicatively is basically the most unefficient way to do it.

  • RogerBlake

    < >

    Using procedural oriented code is totally meaningless in SQL. SQL is optimized if and only if it is *not* procedural. Besides set oriented processing is orders of magnitude much more powerful then procedural resource consuming loop holes.

    < >
    No. That’s the second dumbest thing. The dumbest thing is to put business logic anywhere else than in the dbms. What is the business logic anyway etc a set of constraints that bring meaning to data: question is what can best implement constraints: DBMS. What is better spend time searching tons of mixed up code generated by a confused spaghetti coder or find it a specific centralized place?

    < <. A change in the sproc or C# can, and usually does, break the other. This is a case of the Don't Repeat Yourself principle the Pragmatic Programmer guys talk about. >>
    Then you should review your HR dept qualifications required for people you hire in db design. That is a competence issue. If you hire a developper to do dbms don’t expect him/her to do a clean job in such area.

  • Jayant

    Does a stored procedure run slow (In general) than the application code? We have a situation where we have a heavy use of stored procedure and although there is a lot of validation needs to be performed, we take 28 hours to process 60,000 records and this looks way too slow to me.

  • Slevdi Davoteca

    Business Logic? Surely business logic is a combination of two different things:

    1 transformation of data (the job of the application)
    2 preservation of data integrity according to current business rules (the job of the dbms)

    One of the skills of the system designer is to know which things go where. It is always right to get the dbms to preserve data integrity. Applications can do so as well, of course, but should get business rules from a common place.

  • pwstevens

    This topic is a pet peave of mine; I’m constantly battling with developers to not do this; however there are cases that I can’t see how to eliminate the database business logic. Have you found a way around this scenario (note: this is a trivial example since a simple foreign key could solve this problem… but conceptually the problem exists in other areas where a FK wouldn’t work):
    Rule:
    A Customer can’t be deleted if he has any orders.

    Possible business flow:
    1. Object checks to see if any orders exist, none do.
    2. Someone else enters order
    3. Customer is deleted

    Putting the business check in the SPROC would then put the rule in a context of a transaction.

    Any way to avoid this (note: see note abou FK above).

    Thank you.