I’m working my way through the last of the 201 lessons. Today we’re going to dive back into stored procedures. This time I want to cover the three ways you can get data back out. You can simply use a SELECT statement to return a record set, or you can define one or more of…
Tag: SQL201
SQL201-On Handling Errors
Let’s get back into SQL 200 by learning to deal with errors. Churchill had a great quote that applies to development (database or not) “He who fails to plan is planning to fail.” No matter how well you think you’ve planned your code, it’s going tofail. The question is, will it fail gracefully,…
SQL 201-Non-Correlated Sub Queries
It’s been a while since I’ve covered any material for my SQL 201 series, so I thought I’d jump back into it with non-correlated sub queries. It’s a mouthful. But it’s a really simple concept, you can join two queries that have absolutely nothing to do with each other into one statement. I’d like you…
SQL Homework—UNION, INTERSECT, EXCEPT—Solutions
Here are the solutions to the homework problems I sent out. Compare my queries with yours. If they’re different, and you’d like some explanation on why they’re different. Let me know. I’ll be happy to explain any differences with you. 1. Give me a list of all the first and last names in both tables. …
SQL Homework–UNION, INTERSECT, EXCEPT
This homework will run from my training server, shaioshin.hopto.org. If you do not currently have access to this server, please let me know and I’ll set up credentials so you can complete the following homework assignment. In the AdventureWorks database on my server I have two tables: example.person and person.contact. Take a look at the…
SQL201-The Syllabus
It’s that time again! Time to start up a new class and cover some more advanced topics in SQL Development. Before beginning these lessons, You need to be comfortable with all the material in my SQL101 series. Specifically you’re going to need to know SELECT, INSERT, UPDATE, and DELETE. With those skills in hand, I’ll…
Converting VARCHAR to DATETIME
Recently a post was made on LinkedIn asking how one would convert a date stored in a VARCHAR field could be converted to a DATETIME so date math could be performed. Unfortunately, this problem is more common than it should be. If users are given a free-form field to enter data, they’ll often enter unreliable…
UNION, INTERSECT, and EXCEPT
Earlier today I was discussing methods you can use to differences in data sets between two tables or views. My colleague was discussing the usual method SELECT columName(s) FROM table1 t1 (READCOMMITTED) LEFT JOIN table2 (READCOMMITTED) ON t1.sharedKey = t2.sharedKey WHERE t2.sharedKey IS NULL While this is a perfectly cromulent way of finding out what…
SQL 201-Formatting Dates in Microsoft SQL
One of the more frequent questions I get for SQL Server is how to format a DATETIME into a specific date format. Here’s a summary of the different date formats that come standard in SQL Server as part of the CONVERT function. Following the standard date formats are some extended date formats that are often…
Find Stored Procedures Referencing…
Time and time again, I find myself trying to find all the stored procedures that reference a specific database object. It usually comes around when I’m working on changes to a table’s structure, or I’m replacing a table with a view. I’ll usually reach for the following query (or something like it. 1: SELECT ROUTINE_NAME…