When it comes to CRUD operations and your database (SQL Server '08), is it better to write the SQL statements into your code or use stored procedures? Why?
As pointed out below, I omitted LINQ as a third option. This was done because I am not familiar with LINQ. . . yet. If LINQ is a better option, please let me know what I'm missing.
if it comes down to writing sql in your code vs using stored procs then please use stored procs...they're at least a bit more secure.
With sql in your code, it looks messy and you really open yourself up to sql injection attacks.
On the other hand, unless you really need to use stored procs I'd recommend looking into using an ORM of some sort.
Of those two, Stored Procedures all the way, if for no other reason than it's easier to change a stored procedure in production when it's broken than to deploy new code. And as kristian points out, it's less susceptible to injection attacks (non-parameterized SQL is really bad about this).
There are performance benefits but they're debatably significant.
Jeff (StackOverflow founder) is fond of LINQ to SQL which is different than using Stored Procedures but still safer overall than using inline code.
I much prefer stored procedures over embedding SQL into the app.
For one, when you directly reference tables in your application, the application must have proper permissions on the table. This can be a heightened security risk compared to only allowing the application login to execute certain stored procedures.
Another thing is that if you need to change the SQL query, you possibly need to re-compile and re-deploy the app. In the simplest case this is trivial but it can be cumbersome if your code is distributed and/or if you have multiple different applications that talk to the same database. Now you have to make the change in multiple places, even if the interface or result of the query remains constant.
Finally, stored procedures practically force you to strongly type your parameters (yes, you can still pass in strings composed of multiple values, and you can still use dynamic SQL if you want, but you have to try harder to do that). SQL in an app tends to be much more problematic in terms of building strings on the fly and exposing yourself to SQL injection. Yes, you can build better, parameterized statements in application code, but quite frankly, this is the exception rather than the rule, in my experience.
Depends on your preferences.
The world used to love Stored Procedures because they were more secure (inline SQL statements were built by concatonation which opened you to SQL Injection). They also have the added benefit of allowing you to modify the database without having to modify your code and re-deploy.
Recently, parameterized queries (using queries with parameters rather than building them through concatonation) have gained popularity. They offer the same security as stored procedures (no SQL Injection) with the added benefit of having all your code in one place. That makes it much more apparent that you need to change other elements of code with your CRUD operations change.
At this point...it's personal preference. Personally, I prefer parameterized queries in code so I'm not chasing code all over gods green earth.
And I'm not the only one who (depending on the situation) feels this way and I can't possibly re-iterate all the awesome points made in Jeff's blog post:
Definitively is better to write your Stored Procedures than locate the statements into your code.
Here's why:
These are just a few of the security and better practice features of SQL SPs
Another advantage of stored procs is that you do not have to give permission at the table level (unless you use dynamic SQl which is to be avoided for many reasons). This means your internal users can;t directly access the tables making it less likely that they could do something other than'what the stored procs allow. This helps reduce the possibility of fraud.
A further advatage of stored procs is that they are easier (in my opinion) to performance tune.
Finally, if your database serves mulitple front ends, stored procs are a good way to standardize querying between the applications.
Stored Procedures and CRUD never made much sense to me, particularily in the case of the R (Read)
Stored procedures can't be queried. By that I mean you can't project over a stored procedure (return only some of the possible columns) Nor can you select over a stored procedure (return only some of the rows) and my personal favourite, you can't join two stored procedures together.
All of that to say, that you're going to have an enormus number of purpose built queries wrapped by a CREATE PROCEDURE
The win of saved execution plans, is no longer an exclusive feature of stored procs. Parameterised queries also benefit from the procedure cache.
The win of security is not an exclusive feature of stored procedures -- views also provide a certain mesure of security. But then when we're talking about security -- is SQL the gate keeper? Perhaps it is, or perhaps more likely its the business engine
In the days of basic client server, where the client was some sort of VB 3 application, which connected directly to SQL Sever, then this discussion would be a moot point -- stored procs and nothing but. But we're not building those kind of apps anymore (are we?)
The choice between dynamic SQL and stored procedures may always be controversial, but I have found this to be the most compelling argument I've read:
Stored procedures are bad, m'kay? (by Frans Bouma)
Especially for CRUD, it's getting pretty hard to justify that stored procedures are better. While dynamic SQL does have downsides, a good ORM and/or Linq gets rid of most of those. If you've tried it both ways on decent sized projects, by all means go with your personal preference if you have that opportunity.
I used to be a hard core stored procedure advocate, but recently I have changed position.
One of the problems with stored procedures is that all your queries have to be defined early, and all queries need to be defined in the stored procedure. Say, for example, I wish to query for users with a certain first name, and the last name does not start with "a", and there is not already a stored procedure doing this (probably not), I have to write a new one. And this is one new stored procedure for handling one specific feature. In the last database project I was working on, the number of stored procedures was growing due to new ways of querying for the same data.
In my current application, I dynamically generate SQL queries based on lambda expressions. This allows me to query for any combination of properties on my domain objects, and let the data access layer convert that into a valid SQL query.
I have a lot less code now, it is easier to understand, and most importantly, it is much much faster to implement a new feature. I do however sometimes use stored procedures for inserting and updating data, as they allow me to validate invalid data, and abort the transaction if the data is invalid.
What about security implications? Well traditionally, stored procedures gave a security benefit, as you could hide your tables away, and only expose stored procedures, and specify which user has access to which stored procedure. But who creates applications where the client accesses the database directly anymore?
I would always have some sort of application layer on top of the database running as a service somewhere (web service, windows service, web application, etc), and that service will implement security. No user can directly access the database.