Using Stored Procedure or Dynamic Query
Read this debate first :
------------------------
Posted @ 11/18/2003 4:15 PM
At my company, we have 3 software products that work against SQL Server, Oracle, and DB2 with the flip of a switch. We have built it using dynamic SQL within our code and all 3 have been around for years. These are big apps and, in the end, they are very maintainable.
Imagine trying to sell your product to a company's IT group and saying that it relies on stored procedures. Guess what...the DBA is going to shoot it down because he knows that as new releases come out, it is going to be a huge pain in the ass for them to update and maintain changes to the SP's. Now tell them that they don't have to do anything. All the SQL is managed in the code itself. Well, you know their reaction.
Maybe an in-house application where I know the back-end will always be SQL Server or Oracle or whatever, I would use SP's and love them. That's just not the case for a product-based application. At least in my experience it isn't.
Marc Hoeppner
Posted @ 11/18/2003 4:14 PM
@Frans: Using CommandBuilder?? - This is definitely _not_ a good idea. It may work for simple tables, but as soon as you start to have more complex scenarios, it stops working. 'Complex' scenarios start with using JOINs and multiple PKs etc.
Frans Bouma
Posted @ 11/18/2003 4:19 PM
Marc: no not using commandbuilder:) Using a component you write yourself (or buy). You feed it the objects it should generate DML for and it generates that DML, including parameters. Execute that and you're set. Cache the result on the client if you will so you don't have to regenerate the statement. A dataset has the schema inside itself needed to generate the code you need. (I use the 'dataset' example, since Scott accused me of writing a blog to sell my O/R mapper LLBLGen Pro which is not the case at all :) ).
Mike
Posted @ 11/19/2003 10:39 AM
I'm surprised that noone has mentioned the merits of using SProcs in a secure environment yet. For those DB's where complete access security defines the method of data access, SProcs are really the only way to go.
Say for example I have a DB using Windows Auth to restrict who can view what data. The DB is being accessed by three or four different apps. Now on that DB I have something like 100 tables, which I do NOT want the casual browser (using Query Analyser) to be able to view. I therefore need to lock down access to all tables. Access MUST then be made through SProcs. I know - I could just allow access to the tables through defined logins. But what if I need two different roles (Staff vs. Managers - defined by NT Domain groups) to be able to retrieve different sets of data from the same tables? And what if the access levels that particular users have are to changed? For example - a Manager can only view payslips for those employees that work directly for him instead of the whole department? That would require the code of three or four applications to be changed. I'd rather change one set of SProcs...
The point I'm trying to make is that if you are developing an application in which any kind of sensitive/personal data is involved, or where Role-based access to data is necessary, or you are required to ensure that this data is kept secure - Dynamic SQL will not be your best friend. In these instances only SProcs will help. In fact I'd go to say that if you are designing/developing with security in mind from the start, then you'd be daft not to use the in-built security that SProcs offer you.
Frans Bouma
Posted @ 11/19/2003 10:49 AM
Mike: the time it takes to create all those stored procedures for each role can also be used to create the views you want. The advantage of that is that you can use views in queries (FROM clauses) you can't do that with sprocs.
People think that in a high-secure environment, stored procedures will be much more secure AND it will take way less effort. This is not true: for each byte you want to return as a select result, you have to write a procedure. That code can also be used to write a view.
Frans Bouma
Posted @ 11/19/2003 1:04 PM
"My experience has been that sprocs do provide a dramatic performance increase. The issue is when is this performance boost needed vs. ease of programming. "
While I most of the time agree with your opinions, I have to say this opinion of yours is just what it is: your opinion, not based on fact, just on personal experience. When I look at the theory behind the sql compiler in sqlserver, I can't conclude anything other than that a dyn. parametrized query and a stored proc run as fast, because both will use a cached execution plan. (yes, dyn. query has to be compiled first, so does the proc, which also will have to be recompiled if you're not careful (and the more code you add to make them flexible the more risk you take to cause a recompile of a stored proc on every occasion)
Guys, this debate about Stored Proc are very interesting, you should read it at :
1. Frans Bouma's Blog --> http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
2. Don't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome)
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx