Make me a believer! (Stored Procedures)

Results 1 to 3 of 3

Thread: Make me a believer! (Stored Procedures)

  1. #1
    Join Date
    Dec 1969

    Default Make me a believer! (Stored Procedures)

    According to the SQL Guru, I would want to use Stored Procedures because:<BR><BR>"Reduces client-server network traffic ... helpful for isolating business rules ... helpful for modularizing code and setting security ... helps isolate the application from schema changes"<BR><BR>But in reality, I tend to lose performance (even with the sp_executeSQL). I don&#039t care about network traffic (SQL Server is on E:, server is C: -- not that I send long drawn-out requests anyway...). Code modularization is lost to the syntatic oddities of stored MS T-SQL ("%foo%" is not &#039%foo%&#039) and security is redundant. I&#039ve never actually met a "Business Rule" -- is it like OOP or COM or COM+ or DNA? (Perhaps somebody would be so kind as to explain what a "Business Rule" has to do with the nuts & bolts of software development below where marketing can see?) Finally, if you want to isolate the application from changes, don&#039t stop with the schema. Put in a middle-layer handler which will convert your basic code to any version of a SQL query you want -- then you won&#039t be SOL when middle management tells you that you&#039re being converted to mySQL [or Oracle or something else] to ensure "alternative platform support".<BR><BR>So, given all that, does anybody have a really compelling reason as to why I -- web developer, not DBA -- would want to write SQL Server stored procedures?

  2. #2
    David Highlander Guest

    Default You say Potatoe, I say Potato

    In other words, you ask 100 developers that question you will get 50 answers one way, and 50 answers another. With 100 different reasons why their way is the best.<BR><BR>The problem with this is that it is really a personal preference. Your SQL guru is correct in saying that MOST SQL stored procedures recduces client-server trafic but that does not mean ALL of them do. <BR><BR>As a devloper you won&#039t care about Network traffic, it&#039s not something you are concerned with; however, keep in mind that as a network admin I would not allow you to keep anything on my server unless I approve. <BR><BR>Therefore you need to find away to meet half way with this guy. Create some stored procedures but not all.

  3. #3
    Join Date
    Dec 1969

    Default pros and cons

    Well there is no &#039correct&#039 answer (or is that a "correct" answer :-)<BR><BR>As always it depends on the circumstances in which the application is developed. If you need true database independence then no, stored procedures are not the way to go. <BR><BR>On the other hand let&#039s say you have an application that performs the same queries many times and lots of users. Stored Procedures would then give you *much* better performance because the SQL is &#039precompiled&#039 and executes faster. (Of course you can write a bad stored procedure that wont perform at all just like any bad SQL...)<BR><BR>As for business rules, it is a good idea to take these out of your front end code and put them somewhere isolated. A stored procedure offers this possibility, but it would be much better to put these rules (together with data access routiens) into components. That way your rules are isolated from both the interface and database layers. A simple example of a business rule is any type of validation. Even something like "An employee needs 3 years service to get additional week of vacation per year." The reasoning behind isolating rules like this is to give application independence. Maybe you code this rule in your ASP app. fine. Now what if you also want other apps to access this database? They have to implement the rule as well. The risk is that one will do it different to the other or not at all! If this rule is in the database (or better still a component in the middle tier) then all applications have same rule applied and NONE of them have to re-code it.<BR><BR>One thing I like about stored procedures is that when you access them via ADO you can use parameters to supply values to the query. This means I no longer have to concatenate strings to create a SQL statement. Not only does this code perform better it is much easier to read and less chance of slip ups (so who hasn&#039t missed a quote sometime and spent ages looking for the bug???) <BR><BR>The other issue of security is worth more than a brush off. Stored Procedures let you restrict permissions to tables and only give execute permission to selected stored procedures. This is an important difference. Say you grant permission to tables for your application users. If im a smart guy, I can submit my own query (independent of the application) and it works since I have permission to the tables. However, If my &#039view&#039 of the tables is actually via the results of a stored procedure then that is *ALL* that I see of those tables... that is: the precise columns and rows that you wanted me to see, not the whole table or database.<BR><BR>to finish up, if you are a developing on a small scale then you probably will never expend the extra effort required to implement stored procedures because the pay off is not there in your environment. However, as applications scale up in terms of performance requirements and complexity, stored procedures have an important part to play.<BR><BR>my advice: follow your instincts - but keep open mind on the possiblilties of other approaches.<BR><BR>hope that helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts