Stored Procedures in SQL

Most of the projects I work on use MS SQL server as a data store. When setting up a database, I usually create stored procedures to interact with the database. There are a number of reasons I do this:

  1. Limit access to the database. By using stored procedures, we can give the web user account access to execute the stored procedures without giving them datareader or datawriter access. This means that the only way this account can interact with the database is through the stored procedure. Very much limits the possibility of getting hit by a SQL injection attack.
  2. All interactions with the database are parameterized. Again, this limits the possibility of getting attacked by SQL Injection.
  3. Keeps the database separate from your source code. Especially important if you have multiple applications that access the same database. This allows you to make changes to the stored procedure without making changes to the application(s). This works as long as you don’t change the parameters or structure of the return results of the procedure.
  4. Makes it easy to do multiple things with a single database call. You can even build transactions in the stored procedure when you need to ensure that all queries are successful once the first one runs. If one of the queries fail, then the entire transaction is rolled back.
  5. For projects with a Data Access Layer (DAL), the DAL is almost trivial to write. Simply call the stored procedures and pass back the results.

For some projects, using stored procedures can add too much complexity. However, I tend to error on this side rather than putting too much SQL into the application’s code.

%d bloggers like this: