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;
comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.