What is Stored Procedure's ..? What's the need to use Stored Procedures..?
In this artical i'm trying to explain about Stored procedure.
What is stored procedure..?
What's the need to create Stored Procedures..?
Syntax of Stored Procedures in SQL...?
How to call a SP from .Net Application..?
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 performence of apllication 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 DataBase
SQL statements compile & execute everytime
Stored Procedures only executionSyntax :
CREATE PROCEDURE
[
(parameter1),
(parameter2),...
(parametern)
]
AS
BEGIN
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 arguement to it.Because it is reponsible 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 DataReader call ExecuteReader method on command or if we want to load the data into a DataSet create an object of DataAdapter by Passing command object as a parameter to it and then call Fill method on DataAdapter.
Follow