Jump to content
Can't remember your login details? Read more... ×
Sign in to follow this  

Stored Procedures VS storing commands in a file

Recommended Posts

I'm working on a fairly complex job tracking system. I'm the only person programming and developing it so there isn't much formal planning or collaboration involved in this project.


I have a crapload of SQL commands that I need to run for different tasks. At the moment the database design isn't even finalized and I've found that revisions I have to make to the design are breaking my procedures. Rather than defining all my stored procs now, I'm thinking it might be easier to just store all my commands as variables in an external file.


But if I can do that, I don't completely understand the advantages of stored procedures in the first place. And I also haven' heard of anyone doing something like this before, am I really smart or have i missed something really important?


Any help or advice is greatly appreciated :)

Share this post

Link to post
Share on other sites

A few things to consider:

1) Stored procs prevent SQL injection attacks (provided you don't build SQL expressions within your procedure).

2) Stored procs can allow for more fine grained control over the database and permissions as you can grant permissions specifically to a user or group.

3) If you are building an n-tier system stored procs can push additional load onto your DB server.

4) Many "enterprise" customers require all access to the database via stored procs.


Does your application use any form of ORM layer or have business objects for interacting with the database? It's likely that your files will end up having just as many changes as you will have to make to your stored procs if you are making changes to your database schema, I'd suggest you stay with stored procs and just regularly script them out into files that can be put into source control.


Maybe some time spent on the design now rather than trying to make changes as you develop would be a good idea. Getting your data model sorted early saves a lot of effort.

Share this post

Link to post
Share on other sites

Stored Procedures have benefit of allowing you to put business logic closer to the database. This will more than likely mean less trips across the network, which will result in faster performance.

Stored Procedures have the problem of allowing you to put business logc closer to the database. This will more than likely mean you've got business logic scattered across tiers, resulting in a hotch-potch shit of system to maintain.


A major benefit of stored procs, besides those already outlined by sledgy, is compilation. Because stored procs cannot change (unless you're mad enough to use sp_executesql, then shame on you!), sql server can compile them and store the execution plan. This means a performance saving. Stored procs will also provide another layer of abstraction betweem your business code and your storage layer.


I'm not sure what you mean by "all my commands as variables in an external file". If you mean manually build sql statement in your data abstraction layer, as strings, then that's another perfectly legitimate approach, and one I've used many times.


Whatever you do, you should be splitting the application into business logic, data logic, presentation logic. That way changes to your persistence layer are not effected by your business entities.

Start by breaking your application up and talking to interfaces into your data layer. That way you can continue to code the complex domain logic while not worrying about the still changing database schema. I'm of the opinion that getting your data model sorted early doesn't really make much difference. Often there isn't a one to one mapping between tables and columns in your db and classes and attributes in your domain model. Using DTO's or business entities to abstract your data layer will definitely result in a more maintainable system in the long run. You don't need a complex domain model either, just simple DTO's and some service classes http://www.informit.com/articles/article.a...59&seqNum=5 (it's not just for web apps!)

Edited by kikz

Share this post

Link to post
Share on other sites

Far out!


Cheers for the awesome responses guys. That's exactly what I was after :)


And yep, kikz, I'm going to store the commands as strings.


For the moment the application is going to be used by 10 users at a time (max) on a local network, so performance shouldn't be too serious an issue at this stage.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this