Execute the following script in Query Editor to demonstrate the architecture of an sql stored procedure with INNER JOINs. You can also surround the body of the stored procedure with BEGIN, END. The sproc can be applied to generate a monthly sales detail report in Microsoft Reporting Services or in other BI Delivery software such as IBM/COGNOS ReportNet:

USE AdventureWorks

GO

-- Create a stored procedure with two input parameters

CREATE PROCEDURE procMonthlySalesDetail

    @Year int,

    @Month int

AS

SELECT             SOH.SalesOrderNumber AS SON,

                   SOH.PurchaseOrderNumber AS PO,

                   S.Name AS Store, 

                   convert(varchar,SOH.OrderDate,110) AS OrderDate,

                   convert(varchar,SOH.ShipDate,110) AS ShipDate,

                   '$'+convert(varchar,TotalDue,1) AS [Total Due],

                   C.FirstName + ' ' + C.LastName AS SalesStaff, 

                   SM.Name AS ShippingMethod,

                   BA.AddressLine1 AS BillingAddress1,

                   isnull(BA.AddressLine2,'') AS BillingAddress2,

                   BA.City AS BillingCity, 

                   BSP.Name AS BillingStateProvince,

                   BA.PostalCode AS BillingPostalCode,

                   BCR.Name AS BillingCountryRegion,

                   SA.AddressLine1 AS ShippingAddress1,

                   isnull(SA.AddressLine2,'') AS ShippingAddress2,

                   SA.City AS ShippingCity, 

                   SSP.Name AS ShippingStateProvince,

                   SA.PostalCode AS ShippingPostalCode,

                   SCR.Name AS ShippingCountryRegion,

                   CC.FirstName + ' ' + CC.LastName AS CustomerContact,

                   CC.Phone AS CustomerPhone,

                   SOH.AccountNumber

-- The INNER JOINS are required to get all the detail data  

            FROM       Person.Address SA

            INNER JOIN Person.StateProvince SSP

                 ON SA.StateProvinceID = SSP.StateProvinceID 

            INNER JOIN Person.CountryRegion SCR  

                 ON SSP.CountryRegionCode = SCR.CountryRegionCode 

            INNER JOIN Sales.SalesOrderHeader SOH 

            INNER JOIN  Person.Contact CC

                 ON  SOH.ContactID = CC.ContactID

            INNER JOIN Person.Address BA

            INNER JOIN Person.StateProvince BSP  

                 ON  BA.StateProvinceID = BSP.StateProvinceID 

            INNER JOIN Person.CountryRegion BCR

                 ON  BSP.CountryRegionCode = BCR.CountryRegionCode

                 ON  SOH.BillToAddressID = BA.AddressID

                 ON  SA.AddressID = SOH.ShipToAddressID

            INNER JOIN Person.Contact C

            INNER JOIN HumanResources.Employee E

                 ON C.ContactID = E.ContactID

                 ON SOH.SalesPersonID = E.EmployeeID

            INNER JOIN Purchasing.ShipMethod SM

                 ON SOH.ShipMethodID = SM.ShipMethodID

            INNER JOIN Sales.Store S

                 ON SOH.CustomerID = S.CustomerID 

WHERE Year(SOH.OrderDate) = @Year

AND   Month(SOH.OrderDate) = @Month

ORDER BY Store, OrderDate DESC

GO

-- Test the stored procedure

EXEC procMonthlySalesDetail 2004, 2

GO