SQL Server stored procedure vs an external dll

Question

I am trying to convince someone that using an external DLL to manage sql data is better then using stored procedures. Currently the person I am working with is using vba and calls sql stored procedures to get the complicated data they need from many different sources. It is my understanding that the best way to go about this kind of action is to use a dll/ some intermediate layer to get the data and be able to format it to the needs.

Some things to keep in mind:

• The person i am working with doesn't care to much about being able to scale to much further then we are now
• They don't care to be able to switch to different platforms
• They don't see to much of a performance problem with the current setup
• Using a dll requires more work that is in a different direction
• They don't want to switch if there's not a current problem with doing it the way it is now.(So just because its not the right way wont work...I tried)

So can anyone tell me some benefits of using an external dll then using sql stored procedures ?

Answer 1

I really think it comes down to a matter of preference. Personally I like ORM & saved queries in a DLL vs. Stored Procs, I find them much easier to maintain and distribute than deploying S.Procs to a DB. There are some certain advantages that a S.Proc has over a raw query though. Some optimizations, and some server-side logic that could improve performance in some areas.

All In all though, personally I prefer to work in code than in DB mumbo-jumbo so that's really why I opt for the DLL approach.

Plus you can keep your source code in source-control too, much harder to do with a stored-proc.

Just my 2c.

Answer 2

Agree with the points about controlling the code, much easier in a DLL. Same with source control. However, from a pure performance perspective, the stored procedures will win they day because they are compiled, not just cached. I don't know if it will make enough difference but thought I'd throw that in.

Using stored procedures can also be much more secure as you can lock down access to only stored procedures and you don't (have to) expose your table data to anyone with a connection.

I guess I'm not really answering your question as much as pointing out holes in your argument. Sorry about that but I'm looking at it from their perspective.

Answer 3

Use stored procedures, and write your data access layer which calls them via parameterized commands in a separate dll. Stored procedures are a standard and give you a ton of benefits, parameterized commands give you automatic string safety.

This type of design is basically so standardized and has been for years now that Microsoft has included a framework that constructs it for you in .NET 4.

More or less, both you and this other fellow are right, use sprocs for security, and separate your DAL for security and reusability and lots of reasons

Answer 4

ORM/DLL Approach

Pro:

• You don't have to learn SQL, or stored procedure syntax

Con:

• Complicates multiple operations in a single transaction
• Risks increasing trips between the application and the database, which means data sync/concurrency issues
• Utterly fails at complex queries; most support stored procedures via ORM because of this

You can save SQL, including stored procedures, in flat files. The file extension could be txt, but most use sql - makes storing SQL source in CVS/etc moot vs .NET or Java source code.