Writing Functions and Stored Procedures in SQL Server
| Course Details | ||||||||
|---|---|---|---|---|---|---|---|---|
|
||||||||
| Languages | ||||||||
|
||||||||
| Outcomes | ||||||||
|
||||||||
| Price & Terms | ||||||||
|
||||||||
| Technical | ||||||||
|
||||||||
| Specification | ||||||||
|
||||||||
Write SQL Server functions and stored procedures, explore datetime data, and apply both to a New York City taxi ride case study.
SQL Server lets you wrap query logic in functions and stored procedures so it can be reused, but each tool fits different situations. You explore datetime data in Washington DC BikeShare rides, build and maintain both kinds of modular code, and then put them to work on New York City yellow taxi trips to report borough statistics and plan pickup locations for each driver shift.
What You Will Achieve
- Find the busiest weekdays and outlier rides in timestamped records using DATEPART, DATENAME and DATEDIFF
- Write scalar and table-valued functions that accept one or more inputs and return results you can select from
- Build procedures that insert, update and delete rows, pass back output parameters and trap errors with TRY and CATCH
- Clean impossible values with mean and hot deck imputation before reporting on a dataset
Program Overview
Temporal EDA and Date Manipulation
Exploratory analysis of bike-sharing transactions, plus the variables and date functions that support it.
- Daily transaction counts, busiest weekdays and outliers
- Scalar and table variables with DECLARE, CAST and INSERT INTO
- DATEDIFF, DATEADD, GETDATE and first-day-of-month logic
User-Defined Functions and Stored Procedures
Creating, executing and maintaining both modular tools, and comparing the scenarios each one suits.
- Scalar, inline and multi-statement table-valued functions
- Executing functions and maintaining them with CREATE OR ALTER
- Stored procedures for insert, update and delete operations
- Output parameters, return values and TRY…CATCH error handling
NYC Taxi Ride Case Study
A business case that applies temporal EDA, functions and stored procedures to New York City taxi ride data.
- Finding impossible scenarios, with mean and hot deck imputation
- Case study functions, logical weekdays and a date format for Germany
- Borough statistics by day of the week
- Pickup locations by driver shift
Certificate Awarded (Paid)
Job Outlook
These skills apply wherever SQL Server databases hold transactional records, for example in reporting, data analysis and database development work that depends on reusable query code.
Good for: Analysts and developers who already query SQL Server and want to turn repeated logic into functions and stored procedures they can call again.
Keep in mind: Every exercise is written for SQL Server, with its own date functions and its syntax for functions and stored procedures, and practice runs on BikeShare rides and NYC taxi trips.
Bottom line: A focused SQL Server course that ties functions and stored procedures to datetime analysis and closes with a full New York taxi reporting case.
Frequently Asked Questions
When should I use a function instead of a stored procedure?
The stored procedure chapter compares the two directly and sets out the scenarios each suits. Functions hand back a value or table that a query can use, while procedures carry out actions such as inserting, updating or deleting rows. The case study returns to this choice before you build both.
What data are the lessons built on?
The date and function lessons work with the Washington DC BikeShare transactional dataset. The final chapter switches to New York City yellow taxi ride data, which you clean and then report on by borough and driver shift.
How do the stored procedure lessons handle errors?
Error handling is taught with TRY and CATCH blocks. You see how they work, write your own TRY...CATCH structure inside a procedure, and then catch an error it raises, alongside lessons on output parameters and return values.
How does the taxi case study deal with bad data?
It starts with exploratory analysis to spot impossible scenarios in the ride records. You then fill in problem values with mean imputation and hot deck imputation before writing the functions and procedures that produce borough statistics and shift-based pickup locations.
User Reviews
Write your review
You must be logged in to post a review.
Reviews can only be posted by registered learners. Log in or create a free account, then you will return here to post.
There are no reviews yet.