Question

The following question uses Microsoft SQL Server.

Create a simple stored procedure that does not expect any parameters and contains a single T-SQL statement.

1. Create the stored procedure as PurchaseOrderInfo
2. This stored Procedure should return a result set consisting of ProductName, PurchaseOrderID, PurchaseOrderDetailID, OrderDate, TotalDue and ReceivedQty.
3. Refer to the schema AdventureWorks2012.Production and AdventureWorks2012.Purchasing to access the necessary tables needed to generate the following output.
4. EXECUTE the stored procedure in 2 ways.
   1. Using the simple EXECUTE keyword, and
   2. Using EXECUTE command by adding a WITH RESULT SETS statement.
5. Show the CREATE PROC, both the EXECUTE statements and the two sets of result sets.
6. The result should yield 8845 records.

Answer 1

1. Create the stored procedure as PurchaseOrderInfo
2. This stored Procedure should return a result set consisting of ProductName, PurchaseOrderID, PurchaseOrderDetailID, OrderDate, TotalDue and ReceivedQty.
3. Refer to the schema AdventureWorks2012.Production and AdventureWorks2012.Purchasing to access the necessary tables needed to generate the following output.

T-SQL   FYI

Select p.Name as ProductName,po.PurchaseOrderID, po.PurchaseOrderDetailID, ph.OrderDate,ph.TotalDue, po.RejectedQty
FROM Production.Product p INNER JOIN Purchasing.PurchaseOrderDetail po
       ON p.ProductID = po.ProductID
       INNER JOIN Purchasing.PurchaseOrderHeader ph on ph.PurchaseOrderID = po.PurchaseOrderID

---------------------------------------------------STORED PROCEDURE-----------------------------------------------------

USE [AdventureWorks2012]
GO

/****** Object: StoredProcedure [dbo].[PurchaseOrderInfo] Script Date: 27/02/2020 12:37:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:       Anonymous
-- Create date: 27 Feb 2020
-- Description:   This stored procedure return a result set consisting of ProductName, PurchaseOrderID, PurchaseOrderDetailID, OrderDate, TotalDue and ReceivedQty.
-- =============================================
CREATE PROCEDURE [dbo].[PurchaseOrderInfo]
   -- Add the parameters for the stored procedure here, if any
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

-- Insert statements for procedure here
   Select p.Name as ProductName,po.PurchaseOrderID, po.PurchaseOrderDetailID, ph.OrderDate,ph.TotalDue, po.RejectedQty
FROM Production.Product p INNER JOIN Purchasing.PurchaseOrderDetail po
       ON p.ProductID = po.ProductID
       INNER JOIN Purchasing.PurchaseOrderHeader ph on ph.PurchaseOrderID = po.PurchaseOrderID
END

GO

EXECUTE the stored procedure in 2 ways.

1. Using the simple EXECUTE keyword, and
2. Using EXECUTE command by adding a WITH RESULT SETS statement.

1. SIMPLE EXECUTE ---> EXECUTE PurchaseOrderInfo

2. Using EXECUTE command by adding a WITH RESULT SETS statement.

EXEC PurchaseOrderInfo
WITH RESULT SETS
(   
([ProductName] nvarchar(100) NOT NULL,
[PurchaseOrderID] int NOT NULL,
[PurchaseOrderDetailID] int NOT NULL,
[OrderDate] date not null,
[TotalDue] numeric not null,
[RejectedQty] int not null )
);
  

1. Show the CREATE PROC, both the EXECUTE statements and the two sets of result sets.
2. The result should yield 8845 records.