Stored Procedures vs. ???

6 Comments

• That, in my opinion, is an unintelligent response from Jesse, because the point of SPROCS is that they are precompiled, and they allow you to lock down your database by allowing access only to the sprocs and not to direct selects on the tables. Just think if you opened up DELETE permissions on your tables just cause you don't like SPROCs. REAL smart. Not only will your website run faster because the data will be returned faster, but you'll stop SQL Injection Attacks and you won't be leaving the key to your database under the mat.
  

• Just a guess here, but my guess is that since Yukon is the answer to why SP's will be used less ;)
  

• ack, sorry, i really need to go to sleep...that last comment was horrible...what i meant is that I think the new features of Yukon might be why everyone's talking about using SP's less
  

• If you read the asp.net forum thread, you'll notice I defend the usage of Stored Procedures. I wrote a testlayer with stored procedures and one with dynamic generated queries. When tailored to their task (i.e.: the stored procedure didn't have optional parameters (see my tip in a recent blog) and contained the same query as the dynamic generated query) the stored procedures and the dynamic query are both as fast, perhaps the stored procedure one is a little faster, but that's very very minor. This surprised me as well, because I really thought stored procs would have the edge over dynamic parameterised queries. I'll write a little bloggy about this today. :)
  

• Robert: if you use parameterised dynamic queries, you are not open for sql-injection attacks, since you just pass on parameters like you do with a stored proc call. The argument you have about security in the database is indeed the only reason why stored procedures can be a favorite, however if I can execute such a stored procedure you've locked down via tool the security is moot anyway, which means: security should be enrolled everywhere in the call-chain if you allow a small set of users access a particular piece of code.
  
  
  
  
  
  On the DOTNET list there was a debate the other day about windows-integrated security vs sqlserver security and it turned out that windows-integrated security wasn't even possible with ASP.NET and 2 boxes, without doing multiple administration of users (or storing the userid/password in plain text somewhere), which results in sqlserver security anyway, which results in probably 1 connection string used for the complete application, which means you have to implement security yourself IN the application, which results in that stored proc security is very nice, but will not be used since there is only one user connecting to the database anyway.
  

• Take a look at it this way. If you needed to serialize your data as XML, which would you prefer: a hard coded, precompiled method that contained all your xml serialization code like this:
  
  
  
  
  
  void Serialize(Customer c, StreamWriter output)
  
  
  {
  
  
  output.Write("<customer id='"+id+"'>");
  
  
  output.Write("<name>"+name+"</name>");
  
  
  output.Write("<email>"+email+"</email>");
  
  
  output.Write("<phone>"+phone+"</phone>");
  
  
  output.Write("<fax>"+fax+"</fax>");
  
  
  output.Write("</customer>");
  
  
  }
  
  
  
  
  
  (This, of course, is actually a very simlified version, because you would really want to use an Xml based writer or construct an Xml DOM tree instead, so that the InnerXml values where properly encoded).
  
  
  
  
  
  or would a nice method that did all the work for you (a la .NET's XmlSerializer)?
  
  
  
  
  
  As most developers will agree, the MS approach here with attributes and reflection is vastly superior and saves a hell of a lot of time. I would argue that the dynamic SQL approach is superior for all the same reasons.