Open SQL Server
2005 Management Studio, and open a new query window. Type in the following and execute it:
CREATE PROCEDURE [dbo].[OrdersBySalesPersonID]
(
@salesPersonID int
)
AS
BEGIN
SELECT Production.Product.ProductID, Production.Product.Name, i
Sales.SalesPerson.SalesPersonID, Person.Contact.FirstName, i
Person.Contact.MiddleName,
Person.Contact.LastName, Sales.SalesOrderDetail.UnitPrice
FROM Production.Product
INNER JOIN Sales.SalesOrderDetail ON Production.Product.ProductID = i
Sales.SalesOrderDetail.ProductID
INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID i
= Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = i
Sales.SalesPerson.SalesPersonID
INNER JOIN Person.Contact ON Sales.SalesPerson.SalesPersonID = i
Person.Contact.ContactID
WHERE (Sales.SalesPerson.SalesPersonID = @salesPersonID)
END
GO
227
Part III: LINQ to SQL
This creates a new stored procedure called OrdersBySalesPersonID, which returns the product ID;
product name; the ID of the salesperson; the contact??™s first, middle, and last name; and the unit price
of each product for a given salesperson.
Pages:
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373