I was reviewing this application created by my colleague and was wondering, do we need a stored procedure for single insert statements. Could this be not written directly in the code?
First, let me tell you what is a Stored Procedure is. A Stored Procedure is a group of Transact-SQL statements (DDL & DML) compiled into a single execution plan. It contain programming statements that perform operations in the database, including calling other procedures.
A proc can accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch. There are various advantages of a stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, or SP) over inline code. The most important being 'Security' and 'Speed'.
Compilation and storing of the query execution plan - Increases Speed & Reduces Network Traffic
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 in-line 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. This increases the speed of execution and improves database performance.
You can perform an operation that requires hundreds of lines of T-SQL code through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
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.
Centralised repository for DML and DDL code enabling code reuse
Writing queries in a stored proc means that all the DLL and DML code is kept in one centralised place rather than scattered around in various places in an application or multiple application. Moreover, SQL code is more likely written by a DBA or a SQL developer, making Procs the best place to write them. Thus allowing modular programming concept to be used. Resulting in increase in re-usability ( developers know about this code as it is in a central location instead of some application) and quick development, debugging and maintenance.
Indirectly Grant Permission
You can grant users permission to execute a stored procedure even if they don't have permission to execute the procedure's statements directly.
Readability
Static stored procedure code is a lot more readable than adhoc 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 separation of different parts of a statement onto separate lines for easy reading.
But, all this comes with one major con. Maintenance of the database and the proc with time. In our case we had to replicated the proc on multiple databases, thus taking more memory and space than an in-line code. Should i use Stored Procedure or SQL in code is to your discretion. We preferred Stored Procedure over in-line code as its advantages were much more in our case, than space issues.
Comments
Post a Comment