Problem 2 Use SQL to Create Stored Procedures You know that many queries you will need to complete in your web application can be stored with the database. Step 1 Review Creating Stored Procedures Recall that you can create SQL statements and retain them in the database as a stored procedure. Here are examples. It's up to you to create them! This stored procedure one just retrieves the record. CREATE PROCEDURE [dbo].DisplayCategories AS SELECT [CategoryID], [CategoryName], [Description], [Picture] FROM [Categories] Order By CategoryID RETURN 0 This stored procedure deletes a record so it needs an input parameter to identify which record(s) to delete. CREATE PROCEDURE [dbo].DeleteCategories @pCategoryID int AS DELETE FROM Categories WHERE CategoryID = @pCategoryID RETURN 0 Step 1 Create Stored Procedures to insert and update data Create these three stored procedures as practice. When you are done, click the update button to store the statement in the database. CREATE PROCEDURE [dbo].[InsertLogSessionStart] @customerid INT = 0 , @authuser NVARCHAR(200) = NULL, @authpassword NVARCHAR(50) = NULL, @ipddress NVARCHAR(50), @sessionid NVARCHAR(200), @datevisitbegin DATETIME = NULL, @datevisitended DATETIME = NULL, @browser NVARCHAR(50) = NULL, @browserversion NVARCHAR(50) = NULL, @useragent NVARCHAR(MAX) = NULL, @platformname NVARCHAR(200) = NULL, @supportcookies NVARCHAR(50) = NULL, @supportjavascript NVARCHAR(50) = NULL, @inputtype NVARCHAR(50) = NULL, @displayheight NVARCHAR(50) = NULL, @displaywidth NVARCHAR(50) = NULL, @displaybitsperpixel NVARCHAR(50) = NULL, @mobiledevice NVARCHAR(50) = NULL, @mobilemanufacturer NVARCHAR(50) = NULL, @mobilemodel NVARCHAR(50) = NULL, @pathinfo NVARCHAR(MAX) = NULL, @firstpage NVARCHAR(MAX) = NULL, @querystring NVARCHAR(MAX) = NULL, @cookie NVARCHAR(MAX) = NULL, @httpcookie NVARCHAR(MAX) = NULL, @httpvariables NVARCHAR(MAX) = NULL, @referrer NVARCHAR(MAX) = NULL AS INSERT INTO Log ( customerid, authuser, authpassword, ipddress, sessionid, datevisitbegin, datevisitended, browser, browserversion, useragent, platformname, supportcookies, supportjavascript, inputtype, displayheight, displaywidth, displaybitsperpixel, mobiledevice, mobilemanufacturer, mobilemodel, pathinfo, firstpage, querystring, cookie, httpcookie, httpvariables, referrer) VALUES ( @customerid, @authuser, @authpassword, @ipddress, @sessionid, @datevisitbegin, @datevisitended, @browser, @browserversion, @useragent, @platformname, @supportcookies, @supportjavascript, @inputtype, @displayheight, @displaywidth, @displaybitsperpixel, @mobiledevice, @mobilemanufacturer, @mobilemodel, @pathinfo, @firstpage, @querystring, @cookie, @httpcookie, @httpvariables, @referrer) RETURN Scope_Identity() What is new here is you return the identity value using Scope_Identity. TIP: If you have a local server or use parallel processing, retrieving the scope_identity can be a problem. You can use the output clause if your scope_identify is constantly showing you 0 or 1. Basically this means putting the output variable into a table or table variable. Then do not use ExecuteNonQuery which returns an integer, but use the executeScalar method returns an object. For now, just use Scope_Identity! Create the stored procedure to retrieve the logid. Notice here the [ ] are used for the Log so it's not confusing the application with another log. CREATE PROCEDURE [dbo].[GetLogID] @sessionid NVARCHAR(200) AS SELECT logid FROM [Log] WHERE sessionid = @sessionid RETURN Create the stored procedure to update the log table. CREATE PROCEDURE [dbo].[UpdateLogSessionEnd] @logid int, @customerid int, @sessionid NVARCHAR(200), @datevisitended DATETIME AS UPDATE Log SET customerid = @customerid, datevisitended = @datevisitended WHERE @sessionid = sessionid or @logid = logid RETURN @@Identity Step 1 Create the Stored Procedures Now it's your turn!!! Create stored procedures below. Show the stored procedure and the data returned in your Word document. There are quite a few but you will use these later when you build the web site. Return a list of all categories, sorted by category ID Return a list of all products, sorted by product ID Return a list of all orders, sorted by order ID Return a list of all customers sorted by customer ID Return a list of all log file activities sorted by log ID Return a list of all products in a specific category, given a category ID as an input parameter Return a List of all customers sorted by Last name, only returning the last name, first name and customer ID values Return all the fields for a specific customers given a customer ID as an input parameter Return all the orders for a specific customers given a customer ID as an input parameter Return all the order information for a specific order, given the order ID as an input parameter Return a list of all orders sorted by total amount of the purchase. Return a list of all products that have been ordered, and show the total profit (Price - cost), sorted by the profit in descending order Return a list of all products sorted based on the profit Return a list of web sites that had links to your site, that the customers clicked on to access your site, sorted by the number of times they were referred. (You will get this information from the server variables later in the course) Return a total number of visitors on the web site, based on the log table