Database Reference
In-Depth Information
CREATE PROCEDURE dbo.PersonByFirstName
@FirstName NVARCHAR(50)
AS
--gets anyone by first name from the Person table
SELECT p.BusinessEntityID,
p.Title,
p.LastName,
p.FirstName,
p.PersonType
FROM Person.Person AS p
WHERE p.FirstName = @FirstName;
GO
CREATE PROCEDURE dbo.ProductTransactionsSinceDate
@LatestDate DATETIME,
@ProductName NVARCHAR(50)
AS
--Gets the latest transaction against
--all products that have a transaction
SELECT p.Name,
th.ReferenceOrderID,
th.ReferenceOrderLineID,
th.TransactionType,
th.Quantity
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th
ON p.ProductID = th.ProductID AND
th.TransactionID = (SELECT TOP (1)
th2.TransactionID
FROM Production.TransactionHistory th2
WHERE th2.ProductID = p.ProductID
ORDER BY th2.TransactionID DESC
)
WHERE th.TransactionDate > @LatestDate AND
p.Name LIKE @ProductName;
GO
ALTER PROCEDURE dbo.PurchaseOrderBySalesPersonName
@LastName NVARCHAR(50),
@VendorID INT = NULL
AS
SELECT poh.PurchaseOrderID,
poh.OrderDate,
pod.LineTotal,
p.[Name] AS ProductName,
e.JobTitle,
per.LastName + ', ' + per.FirstName AS SalesPerson,
poh.VendorID
