Stored Procedure in Sql

A stored procedure is a group of Transact-Sql statement compiled into a single execution plan. Its faster than ordinary sql statements because they will undergone in a sequence of steps to execute.
By default SPs stored in DB in compiled form, but if we need to create SP that compile each time it runs than we can add 'with recompile' at the time of creation.

Simple syntax -
create procedure procedureName
[@param1 datatype, @param2 datatype, [@param3 datatype out,]...]

[with RECOMPILE]

as
Begin
--Sql statements
End

A procedure can take multiple parameters, and can return -
1. Data like single int or string value.
2. Can return a local or global cursor.
3. Can return a result set for all select statement(and/or stored procedure) contained in SP.

Benefits -
1. Faster execution - Stored procedure is faster than T-sql statement, because they stored in compiled form in database. Parsing and optimization done only once when fist time executed.
2. Modular programming - Stored procedure need to create once and store in database than one can call it any number of time into the program.
3. Reduces network load - Instead of sending hundreds of T-sql statement's set, a small stored procedure execute statement can be sent over network.
4. Security - Permissions can be assigned to users on stored procedure, even when a user doesn't has permission to execute contained statement directly.

Ex. 
create proc myProc1
@paras1 int, @paras2 varchar(10) out, @paras3 money out
as
begin
 select @paras2 = empname,@paras3 =Salary from tbl_employee where id = @paras1
end

--Calling
declare @myresult varchar(10), @sal money  --Output parameters must be declared before execute statement
execute myproc1 3, @myresult out, @sal out
print @myresult+ ' has '+ cast(@sal as varchar(10))