SQL Stored Procedure In SQL Server Management Studio

-


The main purpose of stored procedures to hide direct SQL queries from the code and improve performance of database operations such as SELECT, UPDATE, and DELETE data.SQL stored procedure is a collection of SQL commands and SQL logic. It also can be use as caches and reused.

Type of stored procedure

There are two types of stored procedure:
  1. User defined stored procedure
  2. System defined stored procedure

User defined stored procedure

It developed by database developer or database administrators. These Stored Procedure contains one or more SQL statements to SELECT, UPDATE or DELETE records from database table. It can take input parameters and produce output parameters.It is a mixture of Data Definition Language and Data Manipulation Language commands.

User Defined Stored Procedure can classified into two types:
  • T-SQL Stored Procedure
                       Transact SQL (T-SQL) Stored Procedures receive and return parameters. It process the INPUT, UPDATE and DELETE queries with and without parameters and return data of rows as output.It most common way to write Stored Procedure in SQL Server.
  • (Common Language Runtime ) CLR Stored Procedure 
                       These are written in a CLR based programming language such as C# or VB.NET and executed by .NET framework

System Stored Procedure

It created and executed by the SQL server for server administrative activities

Benefits of  SQL stored procedures

  1. Reduced server / client network traffic  : 
      2. The commands in a procedure are execute as a single batch of code.It only call to execute the procedure is sent across the network. Due to this, it can reduced network traffic between the server and client. Every individual line of the code would have to cross the network.
    1. Stronger security : 
        2. Even if users and programs don't have permission on those underlying objects, multiple users and client programs can perform operations on underlying database object through a procedure.  That procedure controls what processes and activities are performed and protects the underlying database objects.It eliminates the requirement to grant permission at the individual object level and simplifies the security layers.  
        When calling a procedure over the network, it only call to execute the procedure is visible. Therefore, malicious user cannot see the table and database object names. Procedure parameters helps guard against SQL injection attacks.Since parameter input is treated as a literal value and not as executable code, it more difficult for an attacker to insert a command into the Transact-SQL statements
      1. Reuse of code :

        The code for any repetitious database operation is the perfect for encapsulation in procedures. This eliminates rewrite the same code, decrease code inconsistency and allows the code to be accessed and executed by any user or application possessing the necessary permissions.

      2. Easier maintenance :

        When client applications call procedures and keep database operations in the data list, only procedures must be updated for any changes into the specific database. The application layers remains separate and does not have to know about database layouts , relationships or processes.

      3. Improved performance :

        A procedure compiles the first time it is executed and creates an execution plan which is reused for subsequent executions by default. Since the query processor doesn't have to create a new plane, it typically takes less time to process the procedure. 
             

      Comments

      Post a Comment

      Popular posts from this blog

      Easy understanding of MVC design architecture

      -
      Image
      There are lots of principle and methodology in software engineering field. Through this blog I am going to describe MVC design pattern. Real life example : Let's consider you are going to buy a suit for your wedding. You supposed to go your favorite tailor's shop and choose your desired suit's design which is shown by helper boy. Helper boy handed over the chosen design to that tailor( Controller ). Tailor collect the suitable items such as cloths material, needle, thread, button ,zip ( Model collect the information from database ).Then tailor stich the suit and give it to helper boy( VIEW ). Helper boy pack that suit with covered bag and give it to you. The same scenario is happening in MVC design architecture. MVC is an architectural design pattern. It's separate application into three main different components such as Model, View, Controller.  These components are having specific functionalities.  Model Model is simple class. It's contain busin
      Read more

      A / B Testing

      -
      Image
      Introduction to A/B testing A/B testing is the method of comparing two versions of  a software or app against each other to indicate which one performs better. Every company has different marketing goals. We can't always ask our customers what they think of our site or emails. For this situation A/B testing plays the major role to improve our content and build a strategy. A/B testing also known as split tests. It's allow to compare two version of something to learn which is more effective. I simply conclude that do you like version A or version B? How A/B testing works  In A/B testing we take a webpage or app screen and modify it to create a second version of the same page. For example if you want to change the image and it's caption font, you may create four pages  Arial with images.  Arial without images. Times new roman with images. Times new roman without images.  These changes can be as simple as a single button, or complete redesign of the pa
      Read more

      Firewall

      -
      Image
      We can easily said that firewall is a program which is prevents unauthorized access to or from a private network. Firewalls are tools that can be used to enhance the security of computers connected to a network, such as LAN or the Internet. They are an integral part of a comprehensive security framework for your network. What firewall do? Defend resources Validate access Manage and control network traffic Record and report on events Act as an intermediary A Personal firewall is important when You surf the Internet at home using an 'always on' broadband connection You connect to the Internet via a public WiFi network in a park, cafe or airport You run a home network which needs to be kept isolated from the Internet You wish to be kept informed when any program on your computer attempts to connect to the Internet Most Personal Firewalls are highly configurable so you can easily create security policies to suit your individual needs.
      Read more