Stored procedures are most useful in sql server. So lets start with stored procedure first.
Stored procedure: stored procedure is nothing but a stored programme on sql server which is compiled once and executed many times.
Advantages of stored procedure:
1. Saves times to execute code every time.
2. You can provide parameters and it gets executes the saved stored procedure.
3. No need to send full query to sql server to get results.
4. Safe as compared to executing full query as it is executed on sql server.
Syntax to create stored procedure:
create proc procedure_name
As begin
Your_code
End
Examples of simple stored procedure to select all records from employee table:
1. Create simple stored procedure.
create proc select_all_records
As begin
select * from tbl_employee
End
Execute above stored procedure by following method:
Exec select_all_records
2. Create procedure and pass parameters.
Create proc get_records_byid(@eid int)
As begin
Select * from tbl_employee where emp_id=@eid
End
Exec get_records_byid @eid=101
3. Create store procedure and pass two parameters:
create proc get_record_bytwo_para(@e_id int, @e_name varchar(40))
As
Begin
Select * from tbl_employee where emp_id=@e_id and emp_name = @e_name
End
Execute above stored procedure as follows:
Exec get_record_bytwo_para @e_id= 101, @e_name='satish'
4. Use like operatore in store procedure:
Create sp to retrive records from sql server using like operator:
create proc sp_getrecords_usling_like(
@e_name varchar(50))
As
Begin
select * from tbl_employee where emp_name like %@e_name%
end
Execute this stored procedure use following:
Exec sp_getrecords_usling_like @e_name='mahesh'