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

Insufficient access rights to perform the operation. (Exception from HRESULT: 0x80072098)

While accessing the active directory (AD) and authorization manager (AZMAN) , If you get “   Insufficient access rights to perform the operation. (Exception from HRESULT: 0x80072098)  “ message check the    account that is being used to get the LDAP query from AD .  ERROR DETAILS Exception Details:  System.Runtime.InteropServices.COMException: Insufficient access rights to perform the operation. (Exception from HRESULT: 0x80072098) Source Error: Line 154:    'Session("FullName") = System.Security.Principal.WindowsIdentity.GetCurrent.Name.ToString() Line 155: Line 156:    If Not User.IsInRole("Role1") Then Line 157:          Response.Redirect("./Login.aspx") Line 158:    End If  Stack Trace : .... SOLVE IT Steps to do check the app pool rights: Click on the website name that you are having problem with in IIS  In the right panel you will se...
Post a Comment
Read more

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

Do's and Don't SQL

Do's: Writing comments whenever something is not very obvious, as it won’t impact the performance.  (--) for single line  (/*…*/) to mark a section Use proper indentation Use Upper Case for all SQL keywords. SELECT, UPDATE, INSERT, WHERE, INNER JOIN, AND, OR, LIKE. Use BEGIN... END block for multiple statements in conditional code  Use Declare and Set in beginning of Stored procedure Create objects in same database where its relevant table exists otherwise it will reduce network performance. Use PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities. If User table references Employee table than the column name used in reference should be UserID where User is table name and ID primary column of User table and UserID is reference column of Employee table. Use select column name instead of select * Use CTE (Common Table Expression); its scope is limited to the next statement in SQL query, instead of...
Post a Comment
Read more