Let’s say you have the following table on two servers, one in development and one in production. CREATE TABLE person ( personID INT IDENTITY(1,1) , firstName VARCHAR(50) , lastName VARCHAR(50) ) You’re about to implement a new query, but you need to test it out in development before you move it to production. The problem…
Tag: SQL201
Code to Write Code
I’ve read “When you start writing code that writes code for you, you’ve moved up from a beginner to a professional.” Today I’d like to walk you through an example of where learning to write this kind of code pays off. At my we have a table that defines a hierarchy. Each level on the…
Get Rid of the Duplicate Records
Time after time I’ll get a table that has duplicates in it and I have to clear out all the duplicate entries while leaving a distinct set of records. There’s a couple of ways to solve the problem. Today I want to take you through my standard fix. Script the source table out to a…
Variables And Parameters
Transact SQL (T-SQL) has two main wait of passing data, variables, and parameters. A variable is an object in T-SQL batches and scripts that can old a value. After you define a variable, you can SET the variable or get the value from a variable at any point after the declaration. Useless trivia: You can…
Flow Control
When you want to build more intelligent or more flexible T-SQL scripts, you’re going to need to know how to control the flow of the program. You’ll need the code to be able to make simple decisions on what to do next. Here are the commands you’re going to learn: IF…ELSE BEGIN…END BREAK CONTINUE WHILE…
The Error Severity
Errors can have different levels of severity. Based on this severity, you may wish to handle the error in different ways (see my articles on TRY..CATCH). In order to determine the severity of an error, Microsoft SQL has provided the ERROR_SEVERITY() function. This function takes no argument and returns an integer. Check out this example:…
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…