Create stored procedures in SQL

A Stored Procedure is a segment of MySQL Statements and Compiled objects stored in the database. They increases the speed of execution of the web application because they stored in the database and executes or compile in the database only first time and only returns the result so they also increases the speed of the execution of the web application which is a very big requirement now a days. One of the big advantage of stored procedure is that the SQL injection does not apply on the stored procedure which is basically a hacking technique. Stored procedures are invoked with the help of triggers, other stored procedures or applications such as Java, C#, PHP etc. They also reduces the traffic between the web application and the database because if we work without using stored procedures then we have to send a set of MySQL statements and if we use the stored procedures then we have to send only the name of the stored procedure.

Before MySQL version 5.0, MySQL do not support the stored procedures but after that version these features were added to MySQL database engine to make it more flexible and powerful.

Steps to create a stored Procedure(in visual studio):

1. Open the Server Explorer from View Menu of the Visual Studio.
2. Open the database for which you want to create the stored procedure.
3. Expand that database and you see the an option for Stored Procedure.
   
4. Now Right click on Stored Procedure and then add new stored procedure.
5. Now a window open like this.

   CREATE PROCEDURE dbo.StoredProcedure3
   (
   @EmpId int,
   @FirstName varchar(50),
   @LastName varchar(50)='',
   @Email varchar(50),
   @PhonNo varchar(50)=''
   @Returnvalue int output,
   @OP int=''
   )
   
   AS
   if(@OP=1)
   begin
   INSERT INTO Employe (FirstName,LastName,Email,PhonNo)
   VALUES (@FirstName,@LastName,@Email,@PhonNo)
   return 1
   end
   if(@OP=2)
   begin
   select * from Employe
   End

6. Here You have to give the name to the stored procedure as StoredProcedure3.
7. Then you have to set the parameters with there data types.
8. Below the As keyword you have to write your MySQL Statements as given in the image below.

   ALTER PROCEDURE dbo.StoredProcedure3
   (
   @EmpId int,
   @FirstName varchar(50),
   @LastName varchar(50)='',
   @Email varchar(50),
   @PhonNo varchar(50)=''
   @Returnvalue int output,
   @OP int=''
   )
   AS
   if(@OP=1)
   begin
   INSERT INTO Employe (FirstName,LastName,Email,PhonNo)
   VALUES (@FirstName,@LastName,@Email,@PhonNo)
   return 1
   end
   if(@OP=2)
   begin
   select * from Employe
   End

9. In the above image i make an stored procedure for Employee table and I write two MySQLstatements for insert data into the table and to select all the fields from the table here in the case of insert statement I will send all the parameters from my web page where I call the stored procedure but kept in mind that all the parameters have the same names.
10. You also make the parameter optional by writing @LastName varchar(50)=’ ’, as in image below. And if you don’t do this and also not send the parameter then it will give error. @LastName varchar(50)=’ ‘, How to call Stored Procedure from the Web Form.

    public Int32 InsertData(String FirstName, String LastName, String Email, String PhNo)
    
        {
            cmd=new SqlCommand();
    
            cmd.Parameters.Add("@FirstName ", SqlDbType.NVarChar).Value = FirstName;
    
            cmd.Parameters.Add("@LastName ", SqlDbType.NVarChar).Value = LastName;
    
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Email;
    
            cmd.Parameters.Add("@PhonNo", SqlDbType.NVarChar).Value = PhNo;
    
            cmd.Parameters.Add("@OP", SqlDbType.NVarChar).Value = 1;
    
            return returnintvalue = con.getIntValue("Employes", cmd);
        }
        public DataSet GetData()
        {
            cmd = new SqlCommand();
    
            cmd.Parameters.Add("@OP", SqlDbType.NVarChar).Value = 2;
    
            ds = con.fetchDataset("Employes", cmd);
    
            return ds;
        }

You have to pass needed parameters with an option parameter which determines which statement will be executed and the name of the Stored Procedure which is Employes in My case as in the image.

Types of Stored Procedure:

• User Defined Stored Procedure:

These Stored procedures are modules or routines that encapsulate code for reuse developed by the user. A stored procedure can take input type parameters, returns result in form of tables or scaler and messages to the client and invoke data definition language and data manipulation language statements, and returns the output parameters. In SQL Server 2008, These are further divided into two types

1. Transact-SQL Stored Procedures.
2. CLR Stored Procedures.

Transact-SQL Stored Procedures

It is a saved collection of Transact-SQL statements that can take and return user-defined parameters as you seen above. For example, a stored procedure might contain the statements needed to insert a new row into one or more tables based on information supplied by the Web application. And the stored procedure might return data from the database to the client application.

CLR Stored Procedures

It added in Sql Server 2005. A CLR Stored Procedure is a reference to a Microsoft .NET Framework. Common Language Run time (CLR) method that can take and return parameters. They are implemented as public or static methods on a class in a .NET Framework assembly.

• System Defined Stored Procedure

These are the stored procedures that are inbuilt and provided by the Microsoft.

For Example:

1. SP_renameDb – Used to rename the Database.
2. SP_Help – To help the users.
3. SP_Addlogin etc.

• Extender Stored Procedure

These are not used now a days usually in MySQL 2005 and above version. And these are used for external component.

Stored Procedure vs Functions

1. Stored Procedures returns value in back-end and only return integer value whereas function returns the values of different data types.
2. We can not use stored procedure in function whereas we can use functions inside functions.