Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

LinkedIn Learning

Program Databases with Transact-SQL

via LinkedIn Learning

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.

Syllabus

Introduction
  • Program databases with SQL Server
  • What you should know
  • Using the exercise files
1. Create Views of the Data
  • Save a query as a view object
  • Explore existing views
  • SchemaBind a view
  • Leverage view objects with indexes
2. Create User-Defined Functions
  • 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
3. Work with Stored Procedures
  • 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
4. Create Transactions
  • Ensure data consistency with transactions
  • Create a transaction
  • Transaction savepoints
  • Automatically roll back transactions
  • Challenge: Create a transaction
  • Solution: Create a transaction
5. Implement Error Handling
  • 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
6. Data Type Conversions and NULLs
  • Implicit data type conversions
  • Understand NULL values
  • The ISNULL function
Conclusion
  • Next steps

Taught by

Adam Wilbert

Related Courses

Reviews

Start your review of Program Databases with Transact-SQL

Never Stop Learning!

Get personalized course recommendations, track subjects and courses with reminders, and more.

Sign up for free