When building solutions in T-SQL, you’ll often be asked to generate a change script. This change script is a batch of SQL statements that when executed sequentially (and completely) will make a change to the database for some business reason. When you build these, you can use every tool you’ve learned about SQL, but there…
Author: slowder
Owego NY
Penny and Mark’s family was having a family reunion, and asked if I wanted to go. I’d never been to Owego, NY before, so I thought I’d go. I got to see the town, meet their extended family and pretty much relax! I also got to go to Spiedie fest. It’s a festival that surrounds…
DELETE Triggers
This is the last of my three posts on triggers. We’ve already covered INSERT and UPDATE TRIGGERS. This time we’re doing DELETE triggers. All the same rules apply to DELETE triggers that apply to INSERT and UPDATE triggers. CREATE TRIGGER t_tablename_delete ON TableName AFTER|FOR|INSTEAD OF DELETE AS T-SQL code Just like for the INSERT TRIGGER,…
Sub Queries With Multiple Levels of Nesting
Let’s go back to sub queries for a bit. You can nest a sub query within a sub query. There really is no limit. But in doing so, you really need to make sure there isn’t a better way, like a join, or Common Table Expression. The following query finds the names of employees who…
Sub Query Fundamentals
Here we are with another sub query post. A sub query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub query. A sub query can be used anywhere an expression is allowed. Here’s an example of a sub query in the SELECT clause of a SQL…
The Error Message
When dealing with errors, it’s often necessary to log, or pass on errors to your users. In either case, you’ve already seen how to trap the ERROR_NUMBER. This time I’ll show you how to get the ERROR_MESSAGE. The ERROR_MESSAGE function takes no argument and it returns a string. BEGIN TRY DECLARE @Number TINYINT, @Result TINYINT;…
Correlated Subqueries in a HAVING Clause
Using sub queries in the HAVING clause is little different than using a sub query in any other part of the clause. The one thing that may through you is the WHERE clause inside the sub query… it acts as a JOIN criteria, so you might not get the placement right, until you’ve done it…
UPDATE Triggers
Last time we discussed triggers, we covered insert triggers. This time we’re doing UPDATE triggers. All the same rules apply to UPDATE triggers that apply to INSERT triggers. CREATE TRIGGER t_tablename_update ON TableName AFTER|FOR|INSTEAD OF UPDATE AS T-SQL code Just like for the INSERT TRIGGER, we can either run the T-SQL code after the update…
Correlated Subqueries with Comparison Operators
If you haven’t used a sub query yet, I’m surprised. They can be incredibly useful. They can also be a crutch…but like I say time and again, each tool has it’s use. I’d like to go into a little detail on sub queries today by talking about correlated sub queries. Let’s say you wanted to…
Correlated Sub Queries with Aliases
I know I’ve covered sub queries before, but I wanted to make sure I made a specific point. When you create a correlated sub query, you can reference columns from your subquery anywhere in your outer query. Usually you’ll either use it in your SELECT clause or your WHERE clause, but you can use it…