Program Databases with Transact-SQL

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.

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
• Merge rows with COALESCE

Conclusion

• Next steps