5NatDag4

1USE Adventureworks2012;
2
3--separates the CREATE PROCEDURE from USE into their own batches
4GO
5
6--used from chapter 15 slides to test if a stored procedure already exists
7IF OBJECT_ID('PurchaseOrderInfo') IS NOT NULL
8	DROP PROC PurchaseOrderInfo;
9
10--separates the CREATE PROCEDURE from USE into their own batches
11GO
12
13--must be in its own batch, creates stored procedure (T-SQL) named PurchaseOrderInfo
14CREATE PROCEDURE dbo.PurchaseOrderInfo
15AS
16	--selecting the required columns from question
17	SELECT porh.PurchaseOrderID, 
18			pord.PurchaseOrderDetailID,
19			porh.OrderDate, 
20			porh.TotalDue, 
21			pord.ReceivedQty, 
22			pod.Name
23
24	--we found some of the columns in this table by exploring it
25	FROM	Purchasing.PurchaseOrderHeader AS porh
26			--This has the other information we need
27			INNER JOIN Purchasing.PurchaseOrderDetail AS pord
28				--compare our primary and foreign keys in each table
29				ON porh.PurchaseOrderID = pord.PurchaseOrderID
30			--Join on another table to get product name
31			INNER JOIN Production.Product AS pod
32				--again using our primary and foreign key to join tables
33				ON pord.ProductID = pod.ProductID
34
35--Executes our stored procedure
36EXEC dbo.PurchaseOrderInfo
37
38--source: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017
39WITH RESULT SETS
40(
41	([Purchase Order ID] int,
42	[Purchase Order Detail ID] int,
43	[Order Date] smalldatetime,
44	[Total Due] DECIMAL(20,4),
45	[Received Quantity] int,
46	[Product Name] nvarchar(200)
47	)
48);