Stored Procedure

A Stored Procedure is a named group of SQL Statements that have been previously created and stored in the database. Stored Procedures accept input parameters so that a single stored procedure can be used over the network by several clients using different input data.

With Recompile Option: Allows creation of a query plan for the stored procedure everytime it is called.


Syntax for creating a stored procedure from MSDN!
USE AdventureWorks2012;
GO IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees AS SET NOCOUNT ON;
    SELECT LastName, FirstName, Department FROM HumanResources.vEmployeeDepartmentHistory; GO

--Transact-SQL Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]