Stored procedures give us many advantages over using sql scripts and SQL statements that are dynamically created by our windows or web applications. In this article we will outline why we should be aiming to use Stored Procedures to enable our web and windows applications to interact with our SQL Server database.
Rewriting inline SQL statements as Stored Procedures
Many applications make use of inline SQL statements that are created in the windows / web application and then passed to the database. An example of this is a search box on an e-commerce site where a user can enter a product name to search for that product. Our application code that creates a dynamic SQL string might look something like:
' build our sql string by getting the product name out of the textbox txtProductName
Dim SQL As String
SQL = "SELECT * FROM Products " & _
" WHERE ProductName LIKE '%" & txtProductName.Text & "%' " & _
" ORDER BY ProductName"
' execute the dynamically generated SQL command
We could replace by getting our application to call a stored procedure in our database that did the product search. The Stored Procedure takes the ProductName as an input parameter so it knows what products to search for:
CREATE PROCEDURE [dbo][udp_ProductSearch_lst]
SET NOCOUNT ON
SELECT * FROM Products
WHERE ProductName LIKE '%' + @ProductName + '%'
ORDER BY ProductName
SET NOCOUNT OFF
This procedure definition is a bit lengthier than our dynamic SQL statement but when writing more complicated queries the stored procedure can actually be shorter. We have demonstrated the difference between some 'in-line' sql that is generated in our application and a Stored Procedure that is a database object that performs the same functionality. Let us now see why you should use a Stored Procedure rather than in-line SQL.
Compilation and storing of the query execution plan
When SQL Server runs a query it calculates the most efficient method of processing the query and stores it in something called the execution plan. Depending on the query, a good deal of the time taken to execute a query is actually spent on calculating the execution plan.
When inline SQL statements are executed the execution plan must be generated each time the query runs. When a stored procedure is called its execution plan is stored in memory and the next time the stored procedure is called the execution plan is retrieved from memory without needing to be recalculated. This increases the speed of execution and improves database performance.
Enabling of conditional and procedural logic
When sending inline SQL statements from our applications we cannot include any conditional logic.
Stored Procedures enable us to tie as many SQL statements together with conditional login such as IF ELSE, SELECT CASE and looping capabilities such as WHILE loops and CURSORs. This can lead to some very powerful querying techniques as well as dramatically reduce the number of times the application has to open and close a database connection or use the connection objects Execute command. This again helps improve database, network and therefore application performance.
Centralised repository for DML and DDL code enabling code reuse
Keeping all of our queries in Stored Procedures also means that our Data Manipulation Language (DML) and Data Definition Language (DDL) code is kept in one centralised place rather than scattered around various pages / forms / classes of one or more applications. This gives the following advantages:
- SQL code is more likely to be written by a DBA or SQL developer rather then a web or non specialised SQL developer. This should result in better, more efficient code.
- It isolates the code that is needed to reflect changes to the database structure thus making maintenance quicker
- Procedures can be reused from different webpages / forms / classes
- Procedures can be reused by more than one application
- Code reuse means quicker development time
- Code reuse means quicker debugging and maintenance time
- Stored Procedures can be used to log errors in a standardised way by using the RAISEERROR function to write to the error log or by storing the error information in an 'Errors' table or emailing it to the DBA etc. This makes debugging your application easier by seperating out your data access errors from your windows/web programming language errors (VB.NET / C# errors etc).
Protection from SQL Injection attacks
Inline SQL statements are at high risk from SQL injection attacks which can enable a hacker to compromise and wipe out your database.
Because Stored Procedures use parameters to receive user inputted values it prevents SQL injection from being used. There have been no known cases of SQL injection where static stored procedures are used.
Stored procedures parameters also define their data type and length e.g. a FirstName parameter might be of type varchar(20) so preventing any unsuitable variables of invalid data types or lengthier strings from being substituted into the query.
Enabling of strict security model
Frequently an application will define its connection string in one place and so all stored procedure calls will be made using these login credentials of the specified user. This means that this users access to the database can be defined by only allowing them permissions to execute the allowed stored procedures whilst denying them permissions to the underlying database objects such as tables and views that are used by the stored procedures.
The benefit of this is that a hacker could not use the the application connection execute ad hoc SQL statements such as:
- Perform a TRUNCATE TABLE
- Execute a DELETE statement with no WHERE clause (deleting all rows from a table)
- Perform an UPDATE statement with no WHERE clause.
- SELECT sensitive data such as passwords
It also has the benefit that application users can only execute the stored procedures that implement the applications required business rules. It prevents non DBA's / SQL specialists writing poor ad hoc / inline SQL queries that don't conform to business rules. Any queries that do not follow the applications specified business rules can lead to invalid 'dirty' data in the database.
Static stored procedure code is a lot more readable than ad hoc dynamic SQL queries that are concatenated together in an application. The Stored Procedure editor window and Query Analyzer both include color formatting of SQL keywords as well as enabling the easy seperation of different parts of a statement onto seperate lines for easy reading.
In this article we have demonstrated the difference between an ad hoc SQL statement generated in an application with a Stored Procedure that achieved the same functionality. We then examined various advantages of using Stored Procedures over inline SQL statements.
We have seen Stored Procedures can be of great benefit in performance, security, readability, reusability, flexibility (conditional and procedural logic) and coding quality (restriction of coding to DBA / SQL specialist). Get a script that automatically generates SELECT / DELETE / UPDATE / INSERT stored procedures.