SQL - Stored Procedures

Stored procedures are precompiled sql statements.

Sql caches execution plan for stored procedures and on every execution of procedure it fetches execution plan from cache by procedure id and executes it.
So, Sql no need go through steps of syntax checking(step1) and plan selection(step2), this increases performance of application that uses the procedure.

While Sql also caches execution plan for sql statements we execute. But if we change any value sql treated it as new sql statement and cache it again with different name.
Example
Select * from employees where Name like '%Ram%'
Select * from employees where Name like '%Raj%'
Plans for above both statements will be cached but with different Ids, so can't take benefit of it.

Procedure Syntax :

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

       -- Add the parameters for the stored procedure here

       <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

       <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

AS

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;

    -- Insert statements for procedure here

       SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

END