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…
Category: Microsoft SQL
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:…
Triggers Overview
OK, What do you know about triggers? Did you know you can use them to run a bit of T-SQL on data changes? Those would be called Data Manipulation Language (DML) Triggers. You can set those up for tables so that a certain action occurs with each INSERT, UPDATE, or DELETE. You can build as…
Reindex All Tables in a Database
I just wanted to share a quick script that I used to help re-index all the tables in my database. If you have any questions about it, please let me know! USE GO DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘base table’ OPEN TableCursor FETCH NEXT FROM TableCursor…
Procedural, Transact SQL — Batches
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…
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;…