STORED PROCEDURE

Syntax:

CREATING -
    CREATE PROC <sp_name>
    (optional: <parameters> (prefixed with @);
    output parameters followed by OUTPUT when the parameter is created)
    AS
    <functional_code>
    (optional: set value of output parameter;)
EXECUTING - 
    <sp_name> by itself (optional: parameters) (can be used if the SP is the first statement in the batch)
    OR
    EXEC <sp_name> (optional: parameters)

Example: Creating

CREATE PROC [dbo].[XGetCustInvoicesPerYear] @CustID INT,
                                    @StartDate DATE,
                                    @EndDate DATE
AS
      SELECT *
      FROM Sales.CustomerTransactions ct
      WHERE ct.CustomerID = @CustID
            AND ct.TransactionDate BETWEEN @StartDate AND @EndDate
            AND ct.TransactionTypeID = 1;
GO

Example: Executing

EXEC dbo.xGetCustInvoicesPerYear
     801,
     '2013-01-01',
     '2013-12-31';

Example: Altering & Adding OUTPUT Parameter

ALTER PROC [dbo].[XGetCustInvoicesPerYear] @CustID INT,
                                        @StartDate DATE,
                                        @EndDate DATE,
                                        @RowCount INT OUTPUT
AS
      SELECT *
      FROM Sales.CustomerTransactions ct
      WHERE ct.CustomerID = @CustID
            AND ct.TransactionDate BETWEEN @StartDate AND @EndDate
            AND ct.TransactionTypeID = 1;
      SET @RowCount = @@ROWCOUNT
GO

Executing: Capturing OUTPUT Parameter

DECLARE @retrows INT
EXEC dbo.xGetCustInvoicesPerYear
     801,
     '2013-01-01',
     '2013-12-31',
     @RowCount = @retrows OUTPUT;

SELECT @retrows