Arizona State University - IFT 530CW3.2_Kshitij.

CW 3.2 CW3.2 (30 points) IFT530 – Week 3 – CW3.2 – Due Jan 26th (Saturday) midnight -- Refer to T_SQL Recipes.pdf under “T-SQL Resources” link on Canvas -- AdventureWorks2012 database 1. Run the ListCustomerNames st ...

CW 3.2 CW3.2 (30 points) IFT530 – Week 3 – CW3.2 – Due Jan 26th (Saturday) midnight -- Refer to T_SQL Recipes.pdf under “T-SQL Resources” link on Canvas -- AdventureWorks2012 database 1. Run the ListCustomerNames stored procedure code for practice that is listed in Section 17-2 on Page 364-365 in T-SQL recipes pdf provided on the Canvas courseroom under ‘T-SQL Resources’ link. Solution: CW 3.2 Output: Output: 2. Create a simple stored procedure that does not expect any parameters and contains a single T-SQL statement. CW 3.2 a. Create the stored procedure as PurchaseOrderInfo b. This stored Procedure should return a result set consisting of ProductName, PurchaseOrderID, PurchaseOrderDetailID, OrderDate, TotalDue and ReceivedQty. c. Refer to the schema AdventureWorks2012.Production and AdventureWorks2012.Purchasing to access the necessary tables needed to generate the following output. d. EXECUTE the stored procedure in 2 ways. i. Using the simple EXECUTE keyword, and ii. Using EXECUTE command by adding a WITH RESULT SETS statement. e. Show the CREATE PROC, both the EXECUTE statements and the two sets of result sets in the screenshot. f. The result should yield 8845 records as shown below in both screenshots. Solution: CW 3.2 Output: Output: CW 3.2 CW 3.2 Output: 3. ALTER the above stored procedure using input and default parameters. a. Write the ALTER procedure for PurchaseOrderInfo b. Add two parameters: ALTER PROCEDURE [dbo].[PurchaseOrderInfo] @EmployeeID int, -- input parameter @OrderYear int = 2005 -- default parameter c. Next add a criteria to your T-SQL statement in your procedure to limit the result based on the values of the two parameters. poh.EmployeeID = @EmployeeID AND YEAR(poh.OrderDate) = @OrderYear d. This altered stored procedure should execute successfully. e. Now, EXECUTE the stored procedure by passing 258 for the input parameter and 2006 for default parameter. CW 3.2 f. Show the ALTER PROC, EXEC statement with the two passing parameters and the result screenshot. g. The result should yield 45 records as shown below. (Note: You should get records for every order date placed in 2006.) Solution: Output: EXECPurchaseOrderInfo258, 2006; CW 3.2 Output: 4. Drop the procedure using DROP PROC command. NOTE: You should ensure to validate if the stored procedure you are looking for (PurchaseOrderInfo) exists, before dropping it. Solution: Output: CW 3.2 5. Create a stored procedure that utilizes an OUTPUT parameter. a. Your procedure should be able to list lastnames of any alphabet you pass through as the first letter (such as 'B%') and also return how many rows have the lastnames starting with letter 'B'. b. Your output should yield a list of 1205 records. Solution: Output: CW 3.2 Output: 6. Execute the code given in Pages 370-371 under Section 17-6 (T-SQL recipes pdf) to understand another example in using OUTPUT parameter. Solution: CW 3.2 Output: