DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to build an SQL stored procedure with INNER JOINs?

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE