Developing_and_Implementing_Stored_Procedures_Lab
docx
keyboard_arrow_up
School
Merritt College *
*We aren’t endorsed by this school
Course
3A
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
13
Uploaded by ElderEnergyGiraffe3
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
CREATE the Following Stored Procedures
Note: Make sure to execute and test each stored procedure after creation to assure that it is running properly. Also, aggregates
will be needed to calculate the proper results for your result set
. Meaning a Group By clause will be needed.
Database = Adventureworks
1.
Name: CREATE PROCEDURE proc_TerritorySalesByYear
a.
Parameter: OrderYear
(Passing Value)
b.
Display the Total sales by territory for the Year Parameter
(The following is for the results set
, which will be created in your statement in order to pass your parameter to receive the
total sales by each territory
)
Page 1
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
Page 2
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
2.
Name: CREATE PROCEDURE proc_SalesByTerritory
a.
Parameter: Territory Name
(Passing Value)
Page 3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
b.
Results set: Display Total sales by Year for the Territory Name
Parameter
(The following is for the results set
, which will be created in your statement in order to pass your parameter to receive the
total sales by each year
)
Page 4
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
3.
Name: CREATE PROCEDURE proc_TerritoryTop5Sales_ByProduct
a.
Parameter: Territory Name
(Passing Value)
b.
Results set: Top 5 Products by year
(The following is for the results set
, which will be created in your statement in order to pass in Territory Name to receive the Top 5 Products sold (Sum of Line Total) by each Year
)
Page 5
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
Hint: You may need to use a temp table/table variable combination
. 4.
Stored Procedure with Output Parameters a. Add a MgrID column to your emp table.
b. Populate it accordingly using the integer data type and same number of characters as the empID column
c. Build a SP that passes in empID and returns an output parameter
of the mgrID - (Create the SP and verify it works correctly) (Keep in mind that the mgrID will also be an individual’s empID., since managers are also employees)
d. (Start a New Query and separate it from the previous Stored Procedure) Declare an empid int and manager_name Varchar (50) variable
Page 6
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
e. Hard code your new empid variable and Pass it into your new SP to return the mgrid (Use an actual empid in the variable location to test and Pass it into your new SP to return the mgrID)
f. Capture that mgrid in a variable and use that mgrid variable to determine the Managers name
(Create another statement which locates the Manager’s Name by using
mgrID)
e. Print the Managers name
Page 7
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
Page 8
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
Page 9
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
Page 10
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
Page 11
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
Page 12
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
*Developing and Implementing Stored Procedures*
Lab
●
All labs should be built in SQL Server Management Studio and saved as a .sql file
●
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
Page 13