Execute the following SQL Server T-SQL script in SSMS Query Editor to create a stored procedure for monthly sales results in columns for a particular year:

-- T-SQL create stored procedure with table variable

USE AdventureWorks;

GO

CREATE PROC MonthlyPurchaseOrder

           @Year INT

AS

  BEGIN

    DECLARE  @MonthlyPurchaseOrders  TABLE(

                                           VendorName NVARCHAR(50),

                                           OrderMonth INT,

                                           TotalDue   MONEY

                                           )

    INSERT @MonthlyPurchaseOrders

    SELECT v.Name,

           DATEPART(MONTH,OrderDate),

           TotalDue

    FROM   Purchasing.Vendor v

           JOIN Purchasing.PurchaseOrderHeader poh

             ON v.VendorID = poh.VendorID

    WHERE  v.Name LIKE ('[k-p]%')

           AND YEAR(OrderDate) = @Year

    SELECT *

    FROM   @MonthlyPurchaseOrders

           PIVOT

           (SUM(TotalDue)

            FOR OrderMonth IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] ) ) AS CrossTab

  END

GO

-- T-SQL execute stored procedure

EXEC MonthlyPurchaseOrder   2003

GO