Create stored procedure in sql server 2008

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.