Stored procedure
:
A stored
procedure is a precompile or already
written sql statement that can be saved in database. By using this procedure we
can use a single statement in more times (like code reusability in c++) just
calling that procedure name where ever you want.
Stored procedures had created after Creating
the table. Stored procedures are used for insert and update delete ,select the
records from the table. The most advantage of writing these stored procedure is
no need to write the sql query every time if we use one query five times in
different places just call this procedure . by using this the length of the
code will be reduced and the execution process will be increased.
Syntax for
stored procedure:
Create procedure
procedure_name
As
Begin
--------
SELECT or DELETE SQL STATEMENT-------
end
There are
two types of stored procedures with and without parameter. The above syntax is
without parameter and it is simple insert procedure.
How to
execute procedure in sql server 2008:
By using ‘exec’
Command we can Execute the stored procedure.
Ex:
Exec Procedure_Name
Stored procedure Example with parameters :
Create procedure ActivityInsert
(
@Type varchar(50),
@EmpId varchar(50),
@Category varchar(10),
@Outline Varchar(200),
@Narration Varchar(MAX),
@Remarks Varchar(200),
@Checked Varchar(50),
@Verified Varchar(50),
@Date date
)
as
begin
if(@Type='Insert')
begin
insert into ActivityReport(EmpId,Category,Outline,Narration,Remarks,Checked,Verified,Date,CreatedDate)
values(
@EmpId,
@Category,
@Outline,
@Narration,
@Remarks,
@Checked,
@Verified,
@Date,
GETDATE())
end
else
begin
update ActivityReport set Category=@Category,Outline=@Outline,Narration=@Narration,Remarks=@Remarks,Checked=@Checked,Verified=@Verified,Date=@Date,ModifiedDate=GETDATE() where EmpId=@EmpId
end
end
In the above
procedure “ActivityInsert” is the Name of
the procedure and "ActivityReport" is the table Name. In the Example is the Combination of INSERT and UPDATE procedure in single Procedure.
