Skip to main content

Stored Procedure or In-line code?

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.

Labels:

Comments

Post a Comment

Popular posts from this blog

JavaScript Interview Questions

This is a compilations of all the interview questions related to Javascript that i have encountered.  Q: Difference between window.onload and onDocumentReady? A: The onload event does not fire until every last piece of the page is loaded, this includes css and images, which means there’s a huge delay before any code is executed. That isnt what we want. We just want to wait until the DOM is loaded and is able to be manipulated. onDocumentReady allows the programmer to do that. Q:  What is the difference between == and === ? A: The == checks for value equality, but === checks for both type and value. Few examples: "1" == 1; // value evaluation only, yields true "1" === 1; // value and type evaluation, yields false "1" == true; // "1" as boolean is true, value evaluation only, yields true "1" === false; // value and type evaluation, yields false Q: What does “1″+2+5 evaluate to? What about 5 + 2 +...
1 comment
Read more

Adding a linked Server using the GUI

Adding a linked Server using the GUI There are two ways to add another SQL Server as a linked server.  Using the first method, you need to specify the actual server name as the “linked server name”.  What this means is that everytime you want to reference the linked server in code, you will use the remote server’s name.  This may not be beneficial because if the linked server’s name changes, then you will have to also change all the code that references the linked server.  I like to avoid this method even though it is easier to initially setup.  The rest of the steps will guide you through setting up a linked server with a custom name: To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer. In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”) Add New Linked Server The “New Linked Server” Dialog a...
Post a Comment
Read more

Lookup!!

LOOKUP As the name suggests, Excel gives us the option to lookup for a number or text in a specific area which needs to be stated. If the value is found the corresponding value or text is returned The syntax for LOOKUP is as follows; =LOOKUP( lookup_value , lookup_vector , result_vector )       In the diagram, column D contains varying salaries, against which there is a company car in column E which corresponds to each salary. For example, a £20030 salary gets a Golf, a £35000 salary gets a Scorpio. A LOOKUP formula can be used to return whatever car is appropriate to a salary figure that is entered. In this case, the lookup_value is the cell where the salary is entered (B13), the lookup_vector is the salary column (D3:D11), and the result_vector is the car column (E3:E11). Hence the formula; =LOOKUP(B13,D3:D11,E3:E11) Typing 40000 in cell B13 will set the lookup_value. LOOKUP will search through the lookup_vector to find the matching salary, and return th...
Post a Comment
Read more