Program Databases with Transact-SQL
Overview
Transition from writing one-off queries to creating repeatable code elements. Explore the types of objects that can be deployed to automate systems and maintain database integrity.
This course focuses on a single category of objects within a SQL Server database: Programmability objects. With these objects, database developers can automate systems with triggers, ensure consistent user experiences with stored procedures, protect database integrity with transactions, and implement error-handling routines that enhance the database’s functionality. Join instructor Adam Wilbert as he takes a deep dive into the Programmability folder in a SQL Server database. Using real-world examples, he explores the types of objects that can be deployed to automate systems and maintain database integrity. To follow along, you just need some familiarity with the functionality of a relational database, as well as how to write Transact-SQL (T-SQL) queries. Discover how to write stored procedures that include input and output parameters; create scalar- and table-valued user defined functions; execute batches of commands as transactions; and more.
This course focuses on a single category of objects within a SQL Server database: Programmability objects. With these objects, database developers can automate systems with triggers, ensure consistent user experiences with stored procedures, protect database integrity with transactions, and implement error-handling routines that enhance the database’s functionality. Join instructor Adam Wilbert as he takes a deep dive into the Programmability folder in a SQL Server database. Using real-world examples, he explores the types of objects that can be deployed to automate systems and maintain database integrity. To follow along, you just need some familiarity with the functionality of a relational database, as well as how to write Transact-SQL (T-SQL) queries. Discover how to write stored procedures that include input and output parameters; create scalar- and table-valued user defined functions; execute batches of commands as transactions; and more.
Syllabus
Introduction
- Program databases with SQL Server
- What you should know
- Using the exercise files
- Save a query as a view object
- Explore existing views
- SchemaBind a view
- Leverage view objects with indexes
- Deterministic vs. nondeterministic functions
- Scalar-valued user-defined functions
- IF ELSE and CASE statements
- Table-valued user-defined functions
- Challenge: Create a function
- Solution: Create a function
- Write and execute a stored procedure
- Stored procedure input parameters
- Stored procedure output parameters
- Create a database trigger
- Challenge: Use function in a stored procedure
- Solution: Use function in a stored procedure
- Ensure data consistency with transactions
- Create a transaction
- Transaction savepoints
- Automatically roll back transactions
- Challenge: Create a transaction
- Solution: Create a transaction
- What is error handling?
- Capture errors with TRY and CATCH
- Generate errors with THROW
- Manage transaction control
- Challenge: Add error handling to a stored procedure
- Solution: Add error handling to a stored procedure
- Implicit data type conversions
- Understand NULL values
- The ISNULL function
- Next steps
Taught by
Adam Wilbert
Never Stop Learning!
