STORED PROCEDURES

• In general when we work with queries from front End.
• When front end encounters Cmd.ExecuteNonQuery() front end sends request to the database
• At database side two actions will be performed .

        1]Compilation of the query

         2]Running of the query

• At the time of compilation database will check for the syntactical errors, and if there any Error(s) occur that Error(s) will be sent as it is to the front end.
• How many times the request may send these two actions will be performed .
• But if there are no syntactical errors compilation of the query is not required and will increase burden on the database so performance will be decreased.

Disadvantages of Working with Queries:-

    1)    Unnecessary compilation will increase burden on the Database.
    2)    Application performance is reduced.
    3)    User will get delayed response 
    4)    Possibility of SQL injection attacks. 
    5)    No code reusability. 
  To overcome these disadvantages  Stored Procedures  are used.

 What is Stored Procedure:-

•    A Stored Procedure is a database object which contains precompiled queries.
• Whenever stored procedure is called from front end queries present in stored procedure will not be compiled rather will run directly.

Advantages Using Stored Procedures :-

•   As there is no unnecessary compilation of queries ,this will reduce burden on DataBase.
•   Application Performance will be improved
• User Will get Quick Responses.
• SQL injection attacks are avoided.
• Code reusability facility

 Syntax to create a Stored Procedure:-

    Create Procedure Procedure Name

    @P1Name Data Type(Size),@P2Name Data Type(Size),…………………..

    As

    Begin

     Statements to be executed

      .

      .

     End

Steps to Work with Stored Procedure:-

  1.     Declare The Command Object

Sy: Classname Object Name;

Ex: Sqlcommand Cmd;

  2.     Define Command Object

Sy: Object Name=New Classname(“Stored Procedure Name”, Connection Object)

Ex:Cmd=New Sqlcommand(“DelRecP”,Con);

  3.     Mention Command Type

Sy: Object Name Command Type=Command Type.Value;

Ex: Cmd.Commandtype=Commandtype.StoredProcedure;

  4.     Pass the values to the Parameters using Command object

Sy:-Cmd.Parametes.AddWIthValue(“Parameter..Name..in..DataBase..Stored Procedure”,Value to be stored into the Parmeter);

EX:-Cmd.Parameters.AddWithValue(“PEmpId”,txtEmpId.text)

  5.     Open the Connection

Sy:-Connection Object Name.Open( );

Ex:-Con.Open();

  6.     Execute The Commend Object

Sy: Object Name.Execution Method( );

Ex: Cmd.ExecuteNonQuery

Whenever we use Cmd.Parameters.AddWithValue(“PEmpId”,9).Internally System Will Create a collection table with name parameters like…

Parameter Name	Data Type	Value
@PEmpId	int	9

This Collection table will be sent to database Stored Procedure ,values will be replaced in parameters and then queries will be Executed