Saturday, 31 August 2013

What is Stored Procedure..? Usage of Stored Procedures..?



Stored Procedure's :

 
                 Whenever we want to interact with a Database from an application we use SQL statements. These SQL statements when used with in an application has a problem i.e. when we run the application Statements will be sent to DB for execution where the statements to be parsed / Compile and then execute. The process of parsing takes place each time , because of which performance of application is decreases. To overcome the above drawback write SQL statements directly in Database itself, with in an object known as Stored Procedures .As a SP is pre-compiled block which is ready for execution will directly execute the statements without parsing each time.

Ex: Application Databases
SQL statements compile & execute every time
Stored Procedures only execution

Syntax :
CREATE PROCEDURE
(
(parameter-1),
(parameter-2),...
(parameter-n)
)
AS
BEGIN
--statements
END


1) A SP is similar to a method in our Application, which is a collection of statements.
2) As SP's can also have parameters but passing them is optional. If we want to declare a parameters of sql server prefix the special character "@" before parameter name.
3) A SP can also return a value we use out in oracle and output in sql server.

Create Sample SP :


CREATE PROCEDURE SP_Demo
(
   @SP_Id  INT, -- INPUT PARAMETER
   @error  VARCHAR(100)=NULL OUTPUT -- output parameter
)
AS
BEGIN
    SELECT  SP_Id,SP_Name
    FROM    table
    WHERE   SP_Id=@SP_Id 
END

 

How to call in Application:

 
1) create an object of class command by passing the SP name as an argument to it. Because it is responsible for calling the procedures.

Eg: Command cmd=new Command("SP_Demo",con);

2) Change the commandtype property of command as stored procedure because by default it is configured to call sql stmts.

Eg: cmd.CommandType=CommandType.StoredProcedure;

3)If the SP having any parameters call that parameters using parameters option..

Eg: cmd.Parameters.AddWithValue("@SP_Id",value);

4) If the SP contains insert, update, delete statements in it then call the ExecuteNonQuery method of command to execute.

If the SP contains a select statements in it and if we want to load the data into a Data Reader call Execute Reader method on command or if we want to load the data into a Dataset create an object of Data Adapter by Passing command object as a parameter to it and then call Fill method on Data Adapter.


Conclusion:

 
Using Stored procedures in our applications the performance is increases why because using stored procedures we overcome the compilation time. Hope this will help you some one who are looking for usage of stored procedures..