CS352 - Intermediate SQL Stored Procedures

Course Description

Discover how to extend and apply your knowledge of SQL by incorporating common programming constructs with various SQL commands and functions. Increase the functionality by using Case logic in your procedures which allows you to classify based on business logic. Learn to use transactions, so that if a multi-table or multi-step data change cannot occur, no changes are made. Use loops and cursors to handle SQL logic that needs to repeat in a specific order. Use Try-Catch for alternatives or to give appropriate warnings when SQL cannot complete a command. Learn to put Dynamic SQL into your stored procedures, allowing you to modify sort order, where clauses, or even the table used.

In this course, you will learn to: 

• Use WHILE loops to control data processing iteratively. 

• Declare and manage cursors to process individual rows returned by a multiple-row query. 

• Handle exceptions using Try-Catch. 

• Use exception-handling, such as the TRY…CATCH construct and RAISERROR command, to raise, trap, and handle errors in stored procedures. 

• Write procedures that use OUTPUT parameters to return values to the calling batch or procedure. 

• Use the OUTPUT clause with data manipulation statements to return information about records that were inserted, updated, or deleted. 

• Use dynamic SQL to dynamically construct and execute SQL statements based on variable information. 

Applies Towards the Following Certificates

• Database Technology Certificate : SQL Security and Procedures
• SQL Security and Procedures : Required Courses