Listing 1: T-SQL • Create the T-SQL Application Logic
The SQL application logic creates a pair of stored procedures that handle selecting attributes for a given product's productID, Name, and ListPrice, as well as enabling you to receive as input parameters several important fields associated with the purchase order entity and inserting them in the Purchasing.-
PurchaseOrderHeader table.
create procedure spSelectProduct
as
begin
select ProductID, [Name], ListPrice
from Production.Product;
end;
create procedure spCreatePurchaseOrder
@nEmployeeID int,
@nVendorID int,
@dtShipDate datetime,
@nProductID int,
@nOrderQty smallint,
@mnUnitPrice money,
@mnTaxAmount money,
@mnFreight money
as
begin
declare @nPurchaseOrder int;
declare @dtOrderDate datetime;
set @dtOrderDate=getdate();
insert into Purchasing.PurchaseOrderHeader(
RevisionNumber, Status, EmployeeID,
VendorID, ShipMethodID, OrderDate, ShipDate,
SubTotal, TaxAmt,
Freight)
values(0, 1, @nEmployeeID, @nVendorID, 1,
@dtOrderDate, null,
@nOrderQty*@mnUnitPrice, @mnTaxAmount,
@mnFreight);
set @nPurchaseOrder=@@IDENTITY;
insert into
Purchasing.PurchaseOrderDetail(
PurchaseOrderID, DueDate, OrderQty,
ProductID, UnitPrice, ReceivedQty,
RejectedQty)
values(@nPurchaseOrder, @dtShipDate,
@nOrderQty, @nProductID,
@mnUnitPrice,
@nOrderQty, 0);
end;