What is stored procedure?

Stored Procedure

The stored procedure is nothing but a group of SQL statements and it is database OBJECT  which contains pre compiled and re executable SQL statements as a unit.  By using stored procedure the program execution becomes very fast.

Stored procedures allow 2 types of parameters:

1) Input - is used to pass a value
2) Output - is used to assign a value

Create a stored procedure:  

create procedure <procedure_Name>   (@parameter1 <datatype>    [output, @parameter2........]) 

AS 

Begin ---- <executable statements> ----  

[RETURN]  

END   

Let us consider the following table.

select * from class

Now create the stored procedure as follows.

create procedure sp_myprocedure
as
begin
select  classid, cname from class
end

The following are the ways to execute the stored procedure.

Click on sp_myprocedure and press F5.
Exec  sp_myprocedure
Execute sp_myprocedure