Stored Procedures/Procedure: A stored procedure is a database object which contains precompiled queries. Stored Procedures are a block of code designed to perform a task whenever we called.
Why we need stored procedure: Whenever we want to execute a
SQL query from an application the SQL query will be first parsed (i.e. complied)
for execution where the process of parsing is time consuming because parsing occurs each and every time we execute the query or statement.
To overcome the above problem we write SQL statements or query under stored procedure and execute, because a stored procedure is a pre complied block of code without parsing the statements gets executed whenever the procedures are called which can increase the performance of an application.
Advantages of Stored Procedure:
· As there is no unnecessary compilation of queries, this will reduce burden on database.
· Application performance will be improved
· User will get quick response
· Code reusability facility
How to Create Stored Procedures/Procedure (Without parameter):
Syntax: Create Procedures <Procedures Name>
As
Begin
<Statements> End
Once the Procedure is created it is physically saved on the server as a Database
Object which can be called whenever we required to the user.
We can call the above procedure from anywhere and from any application that is developed using JAVA (or) .NET languages
How to Call a Stored Procedures/Procedure:
Syntax: Exec <Procedure name>
Examples on without parameters Procedures:
1) Write a simple procedure program (with out parameters) to print WELCOME
statement on the query window.
create procedure Test1
as begin
print 'WELCOME TO STOREDPROCEDURES' end
Passing Parameters to Procedures: If we want to pass parameters to procedures then we are using the below syntax.
Syntax: Create Procedures <Procedures Name>
(Passing parameters) As
Begin
<Statements> End
Examples on Parameter Procedures:
1) Write a program to add the two values with Parameters Procedure.
create procedure test2(@a int,@b int)
as begin
declare @c int
set @c=@a+@b;
print 'Addition of two variables are:-'+cast(@c as varchar);
end
2) Write a program to perform arithmetic operations of two values with Parameters
Procedure.
create procedure test3(@a int,@b int)
as begin
declare @x int,@y int,@z int,@s int
set @x=@a+@b; set @y=@a-@b; set @z=@a*@b; set @s=@a/@b;
print 'Add of two variables are:-'+cast(@x as varchar); print 'Sub of two variables are:-'+cast(@y as varchar); print 'Mul of two variables are:-'+cast(@z as varchar);
print 'Div of two variables are:-'+cast(@s as varchar);
end
3)create a procedure to display employee details to the user create procedure spselect
as begin
select * from Employee end
Output: execute spselect
4)create a procedure to accept employee ID and delete the record from employee table.
create procedure spdel
@eid int as
begin
delete from Employee where EmpID=@eid
end
Output: exec spdel 4
5)create a procedure to accept employee ID and update the employee details from employee table.
create procedure spupdate
@eid int,@ename varchar(max),@salary money,@address char(30)
as begin
update Employee set EmpName=@ename,Salary=@salary,Address=@address where EmpID=@eid
end
Output: exec spupdate 1,'kamal',88000,'vizag'
6)create a procedure to add records in employee table. create procedure spinst
@eid int,@ename varchar(50),@salary money,@address varchar(50)
as begin
insert into Employee values(@eid,@ename,@salary,@address)
end
Output: exec spinst 6 ,'Suman' ,41000 ,'chennai'
7)create a procedure to insert records in two tables.
create procedure spinserttwotables
@eid int,@ename varchar(50),@salary money,@Address char(40),@Deptno int,@Dname char(30),@Loc char(20) as
begin
insert into Employee values(@eid,@ename,@salary,@Address,@Deptno)
insert into Dept values(@Deptno,@Dname,@Loc)
end
Output: exec spinsert 7,'mohan',62000,'mumbai',10,'dotnet','hyd'
How To Drop Stored Procedure:
· Drop Procedure <Procedure Name>
Ex: Drop Procedure SP1
0 comments:
Post a Comment