|
Execute the following
Microsoft SQL Server T-SQL example script in SSMS 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 (data source query) to generate a monthly sales detail report in SQL Server 2005 Reporting Services or in other BI Delivery software such as IBM/COGNOS ReportNet:
USE AdventureWorks
GO
-- Create a Stored procedure with two input parameters
-- SQL stored procedure - tsql create stored procedure - transact sql create sproc
-- SQL inner join - sql select statement
CREATE PROCEDURE sprocMonthlySalesDetail
@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 with inner joins
EXEC sprocMonthlySalesDetail 2004, 2
GO
/* Partial results
SON PO Store OrderDate
SO63208 PO13282193436 Ace Bicycle Supply 02-01-2004
SO63156 PO14587116412 Authorized Bike Sales and Rental 02-01-2004
SO63275 PO609182048 Basic Sports Equipment 02-01-2004
SO63194 PO15370118285 Bicycle Lines Distributors 02-01-2004
SO65090 PO5481126157 Bicycle Warehouse Inc. 02-29-2004
SO63258 PO7279181977 Big Cycle Mall 02-01-2004
*/
Related link:
Getting started with SQL Server stored procedures
|
