TotalStuff Stored Procedure in VS .NET

Maker QR Code in VS .NET TotalStuff Stored Procedure

When the visitor enters the Check Out page- they see their order total The TotalStuff stored procedure returns that text to the Active Server Page CREATE PROCEDURE TotalStuff @VisitorID integer AS DECLARE @ProductTotal money@ShippingTotal money@GrandTotal money@TotalText varchar(1000) Select @ProductTotal = ProductTotal@ShippingTotal = ShippingTotal@GrandTotal = GrandTotal From Visitors Where VisitorID = @VisitorID Select @TotalText = 'Product Total: $' + convert(varchar(50)- @ProductTotal) + '<BR>Shipping Total: $' + convert(varchar(50)- @ShippingTotal) + '<BR>Grand Total: $' + convert(varchar(50)- @GrandTotal) Select @TotalText as TotalText GO

Brought to you by ownSky! 360

Passed into the procedure is the ID of the visitor whose totals need to be returned: @VisitorID integer Within the procedure- you will need variables to store the totals: @ProductTotal money@ShippingTotal money@GrandTotal moneyas well as the totals formatted into text for the Check Out page: @TotalText varchar(1000) You then retrieve the totals for the VisitorID passed into the procedure: Select @ProductTotal = ProductTotal@ShippingTotal = ShippingTotal@GrandTotal = GrandTotal From Visitors Where VisitorID = @VisitorID You then concatenate the total variables with text and HTML tags: Select @TotalText = 'Product Total: $' + convert(varchar(50)- @ProductTotal) + '<BR>Shipping Total: $' + convert(varchar(50)- @ShippingTotal) + '<BR>Grand Total: $' + convert(varchar(50)- @GrandTotal) That text is then returned to the calling application: Select @TotalText as TotalText

The VisitorCheckOut stored procedure modifies the visitor's record with their personal information and marks the order as having been placed The procedure is called from the Check Out page CREATE PROCEDURE VisitorCheckOut @VisitorID integer@VisitorName varchar(100)@Address1 varchar(100)@Address2 varchar(100)@CSZ varchar(100)@PhoneNumber varchar(100)@EmailAddress varchar(100)@CCType varchar(100)@CCNumber varchar(100)@CCExpirationDate varchar(100) AS BEGIN Update Visitors set VisitorName = @VisitorNameAddress1 = @Address1Address2 = @Address2CSZ = @CSZPhoneNumber = @PhoneNumberEmailAddress = @EmailAddressCCType = @CCTypeCCNumber = @CCNumberCCExpirationDate = @CCExpirationDateStatus = 'Order Placed' Where VisitorID = @VisitorID END GO Passed into the procedure is the ID of the visitor who is checking out:

Brought to you by ownSky! 361

@VisitorID integerAlso passed in is the personal information that the visitor entered when they checked out through the Check Out page: @VisitorName varchar(100)@Address1 varchar(100)@Address2 varchar(100)@CSZ varchar(100)@PhoneNumber varchar(100)@EmailAddress varchar(100)@CCType varchar(100)@CCNumber varchar(100)@CCExpirationDate varchar(100) You then update the visitor's record according to the parameters passed into this procedure: Update Visitors set VisitorName = @VisitorNameAddress1 = @Address1Address2 = @Address2CSZ = @CSZPhoneNumber = @PhoneNumberEmailAddress = @EmailAddressCCType = @CCTypeCCNumber = @CCNumberCCExpirationDate = @CCExpirationDateThe record is also marked as Order Placed: Status = 'Order Placed' Where VisitorID = @VisitorID

The StoreReceipt stored procedure returns the contents of the visitor's order formatted for the Receipt page CREATE PROCEDURE StoreReceipt @VisitorID integer AS DECLARE @VisitorName varchar(100)@Address1 varchar(100)@Address2 varchar(100)@CSZ varchar(100)@GrandTotal money@ProductName varchar(60)@Quantity integer@ReceiptText varchar(2000) Declare CurItems Cursor For Select ProductName- Quantity from VisitorItems Where VisitorID = @VisitorID Select @VisitorName = VisitorName@Address1 = Address1@Address2 = Address2@CSZ = CSZ@GrandTotal = GrandTotal From Visitors Where VisitorID = @VisitorID Select @ReceiptText = 'Name: ' + @VisitorName + '<BR>Address 1: ' + @Address1 + '<BR>Address 2: ' + @Address2

Brought to you by ownSky! 362

+ '<BR>City- State- Zip Code: ' + @CSZ + '<BR>Order Total: $' + Convert(varchar(20)- @GrandTotal) + '<BR><BR>Products Ordered:<BR>' Open CurItems Fetch CurItems Into @ProductName- @Quantity While @@Fetch_Status = 0 Begin Select @ReceiptText = @ReceiptText + 'Product: ' + @ProductName + '- Quantity: ' + Convert(varchar(10)- @Quantity) + '<BR>' Fetch CurItems Into @ProductName- @Quantity End Close CurItems Deallocate CurItems Select @ReceiptText as ReceiptText GO The procedure expects one parameter- the ID of the visitor whose receipt is to be returned: @VisitorID integer You will need variables to retrieve personal information for the receipt from the Visitors table: @VisitorName varchar(100)@Address1 varchar(100)@Address2 varchar(100)@CSZ varchar(100)@GrandTotal moneyYou also need variables that will be used to retrieve the names and quantities of the products that the visitor ordered: @ProductName varchar(60)@Quantity integerOne last variable will contain the text of the receipt as you build it in this procedure: @ReceiptText varchar(2000) You also need a Cursor object: Declare CurItems Cursor that will be used to return the names and quantities of the products ordered by the visitor: For Select ProductName- Quantity from VisitorItems Where VisitorID = @VisitorID Next- you retrieve the visitor's personal information into your local variables: Select @VisitorName = VisitorName@Address1 = Address1@Address2 = Address2@CSZ = CSZ@GrandTotal = GrandTotal From Visitors Where VisitorID = @VisitorID You place this data concatenated with text and HTML tags into your return variable: Select @ReceiptText = 'Name: ' + @VisitorName + '<BR>Address 1: ' + @Address1 + '<BR>Address 2: ' + @Address2 + '<BR>City- State- Zip Code: ' + @CSZ + '<BR>Order Total: $' + Convert(varchar(20)- @GrandTotal) + '<BR><BR>Products Ordered:<BR>'

Brought to you by ownSky! 363

Now you need to add the products ordered to the receipt So you open the Cursor object: Open CurItems retrieve the first item ordered: Fetch CurItems Into @ProductName- @Quantity and enter a loop so that each item ordered can be processed: While @@Fetch_Status = 0 Within the loop- you concatenate each item to your return variable: Select @ReceiptText = @ReceiptText + 'Product: ' + @ProductName + '- Quantity: ' + Convert(varchar(10)- @Quantity) + '<BR>' before moving on to the next record: Fetch CurItems Into @ProductName- @Quantity After the loop- you release the Cursor object from memory: Close CurItems Deallocate CurItems The text of the Receipt is then returned to the calling application: Select @ReceiptText as ReceiptText