Reloading a Form in C#

I'm been doing some work recently in C# on a Windows Form I'm creating for a pet project.  I'm creating a form that presents customer information and allows the user to scroll through each customer using "Previous" and "Next" buttons.

While working through the form, I was able to get my data into an array and then present the first customer in the form.  I then figured out how to update the index of the array for the next customer, but I couldn't figure out how to present the next data into the form.  Through my research, I found the following post:

Reloads Form in C#

Scrolling through the answers, one answer says this:

try this

this.formName_Load(this, null);

---

Kapul

I then put this code into my program right after I've updated the index of my array for the next customer.  Magically (not really) the form reloaded with my new data.

I wanted to discuss the different portions of the above code and what it does:

• this - refers to the current instance of the class.  For example, you could have multiple classes that have the same form names and each could be referenced by other classes.  "this" makes sure that you are referencing the current class.
• formName - the name of your form.  For example, my form is called "customerForm", so I would replace formName with customerForm.
• _Load - tells the form to load.  To read more on Load event, go here: Form.Load Event
• (this, null) - The "this" again refers to the current class.  null is used in place of the EventArgs class (which is used to provide data for events).  Since this is an event that doesn't contain any data, we then pass a null, which is an empty field.  To read more on the EventArgs class, go here: EventArgs Class

Calculating a Remainder in SQL

When doing math problems using SQL, it is a good idea to consider what type of data you are working with.  In SQL, you will often deal with a data type called Integer.  With integers, if you do any division, all your answers will come out in whole numbers (i.e. you won't get any decimal numbers back).  For example, if you run the following in SQL, the answer will be 2 instead of 2.5:

select 5 / 2

Clearly you can start to lose data if you are expecting decimal places while dividing integers.  It is often better to convert your integers to a data type that allows for decimals.  For example, you can convert your integer data types to numeric and then perform the division.

select cast(5 as numeric(2,1)) / 2

If you don't want to cast your data to another data type because of possible loss of data, you can also use the Modulo operator (sometimes called Modulus in other programming languages).  To use the Modulo operator, replace the divide sign (/) with the percent sign (%).  Using the same example from above, we will use the Modulo operator:

select 5 % 2

The answer to the above would then be 1, which is the remainder of 5 / 2.

We could then display our data using the following query:

select ' 5 / 2 is ' + cast(5 / 2 as varchar) + ' with a remainder of ' + cast(5 % 2 as varchar)

Which gives us the results of

 5 / 2 is 2 with a remainder of 1

To read more on Modulo, go here: Modulo

Arithmetic Operators in SQL

Yesterday we talked about variables.  Today we will talk about an arithmetic operators that can be applied to your variable.

An arithmetic operator is one that applies some type of math to a value.

Let's first look at an example of code.  For instance, if you wanted to add a 1 onto an integer variable, the most basic way to do this is by doing the following:

set @integer = @integer + 1

Since you want to add 1 onto the original value of your integer, you have to set the variable equal to your current value plus 1.  Using the above, however, requires unnecessary retyping.  A better way to approach this would be to use the Add Equal operator, like so:

set @integer += 1

The above will add one onto the current value of the variable @integer, while only using our variable once.  This gives us less of a chance of a spelling mistake.  The same works for the Subtract Equals (-=), Multiply Equals (*=) and Divide Equals (/=) operators.

If we create different integer variables, we can see how each works

declare @integer1 int = 1
declare @integer2 int = 1
declare @integer3 int = 2
declare @integer4 int = 2

set @integer1 += 1
print @integer1

set @integer2 -= 1
print @integer2

set @integer3 *= 2
print @integer3

set @integer4 /= 2
print @integer4

We would get the following results:

2 (1 + 1 = 2)
0 (1 - 1 = 0)
4 (2 * 2 = 4)
1 (2 / 2 = 1)

As a programmer, it is always better not to have to retype code or variables.  In this case, you are saving yourself from retyping variables.


To read more on Arithmetic Operators, go here: Arithmetic Operators

SQL Variables

It's been a while since the last time I blogged.  After a family reunion and a busy week at work, I've been slow to get back into the blogging side of life.

Today I've decided to start off with a subject that is rather easy.

In SQL, as with most programming languages, you will find something called a variable (it may be called something else in other programming languages).  A variable is a set of data that you give a name to and then can assign values to.  For example, in SQL you could have a variable called @pi (the @ symbol tells SQL that this is a variable) and assign it a value of 3.14.  If you need to use the 3.14 value throughout your program, you can then call @pi instead of 3.14 each time.

You might be thinking to yourself "why would I want to create/use a variable instead of just entering 3.14 every time I need it?"  Well, there are many ways to answer this question, but the best answer is that what would happen if you accidentally entered 3.15 in one instance instead of 3.14?  You could potentially throw off the rest of your program.  A variable allows you to use the same value over again without having to re-type it and therefore not leaving yourself open for mistakes.

There are two steps to creating variables:

1. Declare the variable.  You have to let SQL know that the variable exists and give the variable a data type (int, varchar, datetime, etc.)
2. Set a value to the variable.

So, if I wanted to create my @pi variable, I would do it like this:

DECLARE @pi numeric(5,2)

SET @pi = 3.14

You can also declare and set the variable on the same line:

DECLARE @pi numeric(5,2) = 3.14

We can then take that variable and use it:

@Circumference = 2 * @pi * radius

As you dive more into using variables, you will see they are quite useful.

To read more on SQL variables, go here: Local Variables

Using Multiple Databases in SQL

In SQL Server, you can have multiple databases on the same instance of SQL Server.  In SQL Server 2012, you can have over 32,000 (go HERE to read up more maximum capacity specifications for SQL Server) databases in one SQL Server instance.  So, the question is, how do you use/reference all these databases in the same query.

There are two main ways to accomplish this:

1. The use of USE
2. Reference the database in your FROM statement

First, we can use the word USE.  The general syntax for this is:

USE database

Where database is the database you are referencing.  If our Customer table was in the Billing database, it would look something like this:

USE Billing

SELECT *

FROM Customer

Using USE leaves you rather limited, though, because only the one database can be referenced at a time using USE.

Our other option is to specify the database in our FROM statement.  Let's say, for example, that we have our Customer table in the Billing database, while the Orders table is in the Shipping database.  If we wanted to use both tables in the same query, it would look like this

SELECT *

FROM Billing.dbo.Customer

LEFT OUTER JOIN Shipping.dbo.Orders

on Customer.CustomerNumber = Orders.CustomerNumber

In the above, we put the database, a dot (.), the user (in this case dbo, which means database owner), another dot (.) and then the table name.  Using this method, we can query multiple databases at the same time within the same query.

To read more on USE, go here: USE

To read more on selecting your database, go here: Select a Database

Wildcards while Searching Text in SQL Continued

One part of using wildcards that wasn't covered yesterday was the ability to use the keyword NOT in conjunction with LIKE.  Using an example from yesterday, this returns all customers that start with the letter L:

SELECT *
FROM Customer
WHERE CompanyName LIKE 'L%'

If we put the word NOT before LIKE, we will get all customers that do not start with an L:

SELECT *
FROM Customer
WHERE CompanyName NOT LIKE 'L%'

Likewise, we can exclude any customer that has an L in their name:

SELECT *
FROM Customer
WHERE CompanyName NOT LIKE '%L%'

The same works with another one of our examples:

SELECT *
FROM Customer

WHERE FirstName LIKE '_ean'

The above returns all customers that have a name that is four digits and ends in "ean".  The following returns any customer not with four digits AND does not end with "ean".  It could still be four digits, but cannot end with "ean".

SELECT *
FROM Customer

WHERE FirstName NOT LIKE '_ean'

Wildcards while Searching Text in SQL

Let's say you are looking for some text in SQL, but not exactly sure what the text is.  For example, you are looking for any customer where their last name starts with an L.  How would you do that in SQL?

There are two ways you can accomplish this.  The first is by using substring like the following:

SELECT *
FROM Customer
WHERE Substring(CompanyName, 1, 1) = 'L'

This would certainly work, but makes your search NonSARGable (to read up more on SARGable queries, go to this post Non-SARGable Queries)

Luckily, SQL has a logical operator called LIKE (which keeps your query SARGable), that allows you to search for text that matches a specific pattern, as follows:

SELECT *
FROM Customer
WHERE CompanyName like 'L%'

In this instance, the percent sign (%) allows for any characters before or after it, depending on where the % is placed.  The above would look for customers that started with an L.  The following would look for all customers that ended with an L:

SELECT *
FROM Customer
WHERE CompanyName like '%L'

This would look for all customers that had an L anywhere in its name:

SELECT *
FROM Customer
WHERE CompanyName like '%L%'

The % is a wild card option in SQL.  There are other wild card options to consider:

• _ (underscore)
• The underscore represents a single value.  For instance, if you are looking for a four letter name that ends in "ean", you could run the following:

SELECT *
FROM Customer

WHERE FirstName like '_ean'

• [ ]
• A single character within a range or given set.  For instance:

--Range of letters

SELECT *
FROM Customer

WHERE FirstName like '[D-S]ean'

--Set of letters

SELECT *
FROM Customer

WHERE FirstName like '[DEFGHS]ean'

• [^]
• A single character not within a range or give set

--Not in this range of letters

SELECT *
FROM Customer

WHERE FirstName like '[^D-S]ean'

--Not in this set of letters

SELECT *
FROM Customer

WHERE FirstName like '[^DEFGHS]ean'

To read more on LIKE and different wild card options, go here: LIKE

WHILE Statements in SQL

Previously I've spoken about control of flow statements in SQL (see the following: IF...ELSE, GOTO).  Today I will talk about another control of flow statement: the WHILE statement.

A WHILE statement allows you to specify a statement to check and then SQL statement(s) to run while that expression is still true.

The general outline for a WHILE statement is as follows:

WHILE Boolean_expression
{ sql_statement }

• A boolean expression is one that is either true or false
• The SQL statement(s) are the statements to run while the WHILE statement is still true
• If your SQL statement is more than one statement, you will have to begin and end your WHILE statement with BEGIN and END, as seen below.

WHILE Boolean_expression

BEGIN

SQL Statement Number 1

SQL Statement Number 2

END

Following is an example of when a WHILE statement may be useful:

WHILE (SELECT count(*) FROM Customers Where Balance < 50) > 0

BEGIN

UPDATE Customers

SET Balance = Balance * 2

Where Balance < 50

END

The above will update the balance of a customer to their balance times 2 until that balance is above $50.  For example, if we have a customer whose balance is $5, it will go through the WHILE statement 4 times until the balance is $80.

Keep in mind that when WHILE statements are used, it is possible to run into an infinite loop (a loop that never stops) if you aren't incrementing your values correctly.  Using the same query, if I change it a little bit, I can get an infinite loop:

WHILE (SELECT count(*) FROM Customers Where Balance > 50) > 0

BEGIN

UPDATE Customers

SET Balance = Balance * 2

Where Balance > 50

END

The above will never stop if I have one customer that has a balance over $50.  Because they are over $50, our boolean expression is and will always be true.

To read up more on WHILE statements, go here: WHILE

Creating a Child Form in C#

I spent some time this weekend working in C# on a personal project.  One of the subjects I came across was creating a child form.  A child form opens a new form within your current window.

During my research, I came across the following help document:

How to: Create MDI Child Forms

I have a program I created at work that has different tabs for specific tasks that need to be run.  I will be working on migrating from the tabbed format to the child form format, as this seems to be less confusing and the layout looks better.

A few key points for creating a child form:

1. Create a new windows form in your project for each new window.
2. Create an event handler for when you click on the button/menu item that will open the new form (Create Event Handler)
3. Paste the following in your event handler:

protected void MDIChildNew_Click(object sender, System.EventArgs e)
{

   Form2 newMDIChild = new Form2();
   // Set the Parent Form of the Child window.
   newMDIChild.MdiParent = this;
   // Display the new form.
   newMDIChild.Show();
}

Subqueries in SQL

A very helpful feature of SQL is the use of subqueries.  These subqueries allow you to nest one query inside of another query, thus giving you more flexibility with your data.  There are two areas in which a subquery can be used.  The first of these areas is in the SELECT statement.  For example, if we wanted to see how many purchases a customer has made, we can run a query like this:

SELECT CustomerNumber, CustomerName,
(SELECT SUM(Quantity)
FROM Orders
WHERE Orders.CustomerNumber = Customer.CustomerNumber) as Quantity
FROM Customers

In the above example, we used a subquery on the Orders table to get the total quantity (number of purchases) and then the Customers table to get our customer information.

The second instance where a subquery can be used is in the WHERE clause.  This gives us the ability to look for a particular set of data.  As another example, let's say that we want to look for customers that have an order greater than $50:

SELECT *
FROM Customers
WHERE CustomerNumber in
(
SELECT CustomerNumber
FROM Orders
WHERE TotalPrice >= 50.00
)

The subquery returns all CustomerNumbers that have an order of $50 or greater.  We then tell SQL to look for all those (in) CustomerNumbers in our Customers table.  We could also tell SQL to give us CustomerNumbers that are not in the list by putting the word "not" in front of "in, like so:

SELECT *
FROM Customers
WHERE CustomerNumber not in
(
SELECT CustomerNumber
FROM Orders
WHERE TotalPrice >= 50.00
)

This would return all customers that did not have an order of $50 or more.  The word "in" in this situation is called a "logical operator", which we will discuss in my next blog.

To read more on subqueries, go here: Subquery Fundamentals

SQL Joins Part 3

I was doing some research on the CROSS join since I haven't put this join into practice.  I'm still having a hard time imagining when this would be useful.

For purposes of completeness, I will explain how a CROSS join works.

Unlike other joins, a CROSS join does not have an "on" statement where you provide columns for comparisons.  You can insert comparison columns into the where clause, but when this occurs, you are basically running an inner join.

Following is an example of CROSS join, using two tables that are not related in any way.

SELECT *
FROM Customers
CROSS JOIN Vendors

If our Customers table has ten rows and our Vendors table has ten rows, this query will return 100 rows.  Each row from the first table will show with each row in the second table.

Following are two tables that do have comparison columns, with which we will run a CROSS join:

SELECT *
FROM Customers
CROSS JOIN Orders
WHERE Customers.CustomerNumber = Orders.CustomerNumber

We've essentially just run an inner join.

To read more on Cross Joins, go here: Using Cross Joins

What situations do you think a CROSS join could be useful?

SQL Joins Part 2

Yesterday we spoke about OUTER joins.  Today we will talk about INNER joins.  INNER joins will return all rows from both tables, as long as the values in the columns you are joining on match.  This differs from OUTER joins in the fact that OUTER joins could in fact return NULL values for one table.  INNER joins only return those values that match within both tables.

Going back to our example of yesterday, we will use the Customers and Orders tables as examples.  First, lets look at an OUTER join:

SELECT *
FROM Customers
LEFT JOIN Orders on Customers.CustomerNumber = Orders.CustomerNumber

The above would return all Customers and any matching orders in the Orders table, even if no orders exist.  Next, we will use the INNER join, as follows:

SELECT *
FROM Customers
INNER JOIN Orders on Customers.CustomerNumber = Orders.CustomerNumber

This will only return data where it exists in both tables.  If a customer doesn't have any orders, no data will be returned for that customer.

As part of your column matching, you can also use math operators ( >, <, <>).  For example, if we want only orders that are over a certain amount, you would run a query like so:

SELECT *
FROM Customers
INNER JOIN Orders on Customers.CustomerNumber = Orders.CustomerNumber and Orders.Amount > 10.00

This will return only those orders where the amount is greater than $10.

To review, OUTER joins will return data for both tables, even if data doesn't exist (it will return NULLs for non-existent data).  INNER joins, on the other hand, will only return data that exists, based upon your matching columns.

To read more on INNER joins, go here: Using Inner Joins

SQL Joins Part 1

In SQL, there are three different types of JOINs, which, if mastered, can be extremely helpful in querying your data.  The three different joins are:

1. OUTER JOIN
2. INNER JOIN
3. CROSS JOIN

First, with any JOIN statement, you need to specify the columns you will join on.  This tells SQL which columns to use as comparison.  After our second table, we put the word "on" and follow that with our columns comparisons.  As we jump into this post, we will see the "on" used in our FROM clause.

Today I will focus on the OUTER Joins.

Within the OUTER Joins there are the LEFT, RIGHT and FULL JOINs.  These joins are pretty easy to understand.  A query containing a LEFT or RIGHT JOIN will return all rows in the described table (left or right) and matched rows in the other table.  This is defined by which table is on the left (the first table in your FROM clause) or on the right (the second table).  Here is an example if we are using our Customers and Orders tables:

SELECT *

FROM Customers 

LEFT JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

In this example, Customers is on the left, while Orders is on the right.  This will return all rows in the Customers  table and any matching data in the Orders table.

We can then reverse it for another example:

SELECT *

FROM Customers 

RIGHT JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

The above will return all rows in the Orders table and any matching rows in the Customers table.  You can also get the same results by running the following:

SELECT *

FROM Orders

LEFT JOIN Customers on Customers.CustomberNumber = Orders.CustomerNumber

Notice that Orders is the "left" table and Customers is the "right" table.  It may become more evident why you would use LEFT or RIGHT when we reference more than one table.  Let's take our example and add OrderDetails into the mix:

SELECT *

FROM Customers 

LEFT JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

LEFT JOIN OrderDetails on Orders.OrderNumber = OrderDetails.OrderNumber

This query gives us all Customers with matching rows in the Orders table and then all OrderDetails matching with the Orders table.  We could accomplish the same by running the following:

SELECT *

FROM OrderDetails 

RIGHT JOIN Orders on Orders.OrderNumber = OrderDetails.OrderNumber

RIGHT JOIN Customers on Customers.CustomberNumber = Orders.CustomerNumber

We could also mix it up:

SELECT *

FROM OrderDetails 

RIGHT JOIN Orders on Orders.OrderNumber = OrderDetails.OrderNumber

LEFT JOIN Customers on Customers.CustomberNumber = Orders.CustomerNumber

This would give all rows in the Orders table with matching rows in the OrderDetails and Customers table.

A FULL Join will return all rows from both tables.  Any row from one table that doesn't have a match in the other table will return all NULLS for the other table.

SELECT *

FROM Customers

FULL JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

To read up more on SQL Join, go here: Using Outer Joins

GOTO in SQL

I've previously talked about control-of-flow language in SQL.  Another way to control the flow of your programs is using the GOTO.  GOTO allows you to automatically jump to a particular portion of your code, while skipping other portions.

Here is a good example of what GOTO would do:

DECLARE @Counter int;
SET @Counter = 4;
WHILE @Counter < 10
BEGIN 
    SELECT @Counter
    SET @Counter = @Counter + 1
    IF @Counter = 4 GOTO Branch_One
    IF @Counter = 5 GOTO Branch_Two
END
Branch_One:
    SELECT 'Jumping To Branch One.'
    GOTO Branch_Three;
Branch_Two:
    SELECT 'Jumping To Branch Two.'
    GOTO FINISH;
Branch_Three:
    SELECT 'Jumping To Branch Three.'
 
FINISH:
SELECT 'FINISH'

You declare your GOTO section by giving it a name and then following it with a ":".  After you specify your section, you can then put in what you want to happen in that section.

You can specify in your code that you want to go to that section by saying "GOTO" and then the section name.  The above query gives the following results:

4
Jumping To Branch Two.
FINISH

Keep in mind that the program will continue to run from the current GOTO onward if no other GOTO's are specified. If we remove the reference to "GOTO FINISH" in Branch_Two, it will then move to Branch_Three and then FINISH.  Also, you can go to sections before or after the current section.  For example, from Branch_Two, you can then jump back to Branch_One and from Branch_Three you can go to Branch_One or Branch_Two.  Be careful, because if you aren't paying attention, you could put yourself in an infinite loop.

To read more on GOTO, go here: GOTO

Using APIs

I've been spending much of my time recently using APIs (application programming interface) at work, so I haven't had much time to research SQL topics.  For any of you who don't know, APIs are used as a way into an application without providing the code/table diagrams underneath.

For example, if I, as a programmer, want to give people access into my database, but not allow them in directly, I would give them access to an API.  APIs are used for inserting data into the database or retrieving information without giving away what's in your database.

Anyway, I've been spending time recently trying to use some of the APIs from our billing vendor to see if I can increase my work performance.  I'll keep you informed of anything I think worthy of note.

Thanks.

SQL Views

I've briefly spoken about SQL Views in prior posts (see here), so I wanted to take this opportunity to talk a little more about SQL Views.

First, what is a SQL view?

A view is just exactly what it sounds like: it allows you to "view" information.  Views can and are usually created to present data in a certain way.  It is what we may call a virtual table, meaning that the table doesn't actually reside in the database the way it is presented in the view.

A view allows you to take a T-SQL query, spanning across multiple tables if needed, and present it as a view.  Then, someone can write a query using the view and get all the data they need.  This helps to avoid writing the same query multiple times.  The query is already written as a view.

An example might help us better understand the concept.  Let's say that we have three different tables: Customer, Orders and OrderDetails.  We use the following query to get information for an order:

SELECT *
FROM Customer
join Orders on Customer.CustomerId = Orders.CustomerId
join OrderDetails on Orders.OrderId = OrderDetails.OrderId

It can sometimes be difficult to remember the relationship between each table, so instead of creating the same query over and over again, we create a view.  We create a view using the following syntax:

CREATE VIEW [dbo].[vOrderDetail]

AS

SELECT *
FROM Customer
join Orders on Customer.CustomerId = Orders.CustomerId
join OrderDetails on Orders.OrderId = OrderDetails.OrderId

Now, when you want to get the information for a customer order, your query can look like this:

SELECT *
FROM vOrderDetail

To avoid confusion, it is a good practice to name your views differently from your tables.  In my example, I put a lowercase "v" at the beginning of the view name so I know I am using a view.

Once you've created your view, now you can give people access to views.  These are used for just viewing data, users can't update or delete these records.  The deletion or updating of records has to be done on the tables the view refers to.

To read more on views, go here: SQL Views

Querying NULL Values in SQL

When querying a SQL database, you need to be aware of the presence of NULLs.  In SQL, a NULL represents an unknown, non-existent or empty value.  Sometimes the NULL value is put in explicitly or no values was specified for the column, so a NULL was entered.  The reason we need to be careful is we cannot query NULLs the way we do for data types.

For example, let's say that you wanted to query a customer's balance where it is zero (either the customer has paid his balance or he hasn't made any purchases).  Since this would be a money or other number type, you could run a query, like so, to get all your customers:

SELECT *
From CustomerTable
where Balance = 0

But, depending on how you set up your table, new customers may not have a value in the Balance field.  Therefore, this value could be a NULL.  The above query would not return a NULL value, so you aren't getting all the results you require.

There are two ways you can update your query.  Here is the first one:

SELECT *
From CustomerTable
where Balance = 0 or Balance IS NULL

To query on NULL values, you need to specify IS NULL or IS NOT NULL.  This is the only way to query NULLs directly.

The other way to query NULLs is the following.  Keep in mind that this will work, while also making your query NonSARGable:

SELECT *
From CustomerTable
where ISNULL(Balance, 0) = 0

ISNULL() is a function that allows you to convert NULLs in your field to a specified value.  ISNULL() is used like this:

ISNULL( column, value to replace NULL)

We replaced our NULLs with the value of 0.  We can then query all 0s using one simple clause.

To read more on NULL values, go here: NULL Values

Index Design Tips

I've written previously about SQL Indexes.  I've been reviewing tips on Index Design and came across the following Microsoft site:

General Index Design Guidelines

A few things I thought were interesting in the article:

• A large number of indexes on one table can affect your Update, Insert and Delete statements.
• Keep the number of indexes or index columns small on these tables.
• It may be a good idea to create a view and create indexes on the view rather than on the table that will receive Update, Insert of Delete.  This would allow you to modify your original table, but view the data while using a cluster.
• Indexing small tables is not always optimal, since it may take longer to search through the indexes than to find the records.
• Create nonclustered indexes on columns that are referenced frequently in your queries.
• Order the columns in your index based upon how often those columns are used.  For example, if column c is used more than b and b is used more than a, then the column order in the index should be: c, b, and a.
• Columns that are referenced in query using exact matches could be a good candidate for clustered or nonclustered indexes.

These are all good pointers and should get any person inexperienced in creating indexes started.  From there, you can usually play around with different indexes until you find the right ones.

What types have you found while creating indexes on your database?

IF....ELSE in SQL

Every programming language has flow of control ability.  What I mean by this is the language allows you to check your data and make a decision based on what is returned.  One of the most popular ways to control the flow of your program is an IF/ELSE statement.  An IF/ELSE statement allows you to check a value and IF it matches a certain criteria, do something, ELSE do something else.

The basic look of an IF/ELSE statement looks like this:

IF expression
    do this
ELSE
    do this

Let's say that we are building a table of customer data.  Before we continue, we want to check and see if we have any customers in our table.  If we do, print one message or else we print another message:

IF (select count(*) from CustomerTable) > 0
    PRINT 'We have customers'
ELSE
    PRINT 'We dont have customers'

If your block statement after the IF or ELSE does more than one thing, you will need to insert a BEGIN and END at the beginning of each block.  Below is an example:

IF (select count(*) from CustomerTable) > 0
BEGIN
    PRINT 'We have customers'
    PRINT 'That is great'
END
ELSE
    PRINT 'We dont have customers'
    PRINT 'Isnt that sad?'
END

These are very simple examples.  You can do much more than print messages to the screen.  You can also update data, delete data, select data, and much more.  Below is one example:

IF (select count(*) from CustomerTable ) > 0
BEGIN
    delete from CustomerTable
    PRINT 'We just deleted our customers'
END
ELSE
BEGIN
    PRINT 'We dont have customers'
    PRINT 'Isnt that sad?'

END

To read more on IF/ELSE statements, go here: SQL IF/ELSE

Using CONCAT in SQL

In my line of work, we regularly put different fields together for representation to our customers.  There are two ways we can approach this in SQL:

• Concatenate the fields together using the "+" sign
• Using the CONCAT() function

Both of these approaches give the same results in most cases, so why should you use one solution over another?  I would first say because of how it looks.  For example, if we want to concatenate the first name and last name together, here is what our two solutions would look like:

Using "+" sign:

SELECT FirstName + ' ' + LastName from Customers

Using CONCAT() function:

SELECT CONCAT(FirstName, ' ', LastName) from Customers

This is a pretty simplistic example.  If we use something with more fields, it may become more evident.  For example, let's say that we have three different fields for a customer's phone number, we could put the three fields together like so:

Using "+" sign:

SELECT '(' + AreaCode + ')' + CityCode + '-' + Suffix from Customers

Or

Using CONCAT() function:

SELECT CONCAT( '(', AreaCode, ')', CityCode, '-', Suffix) from Customers

Another reason to use the CONCAT() function is that integer values are automatically converted to string.  If, for example, you put numbers together when using your "+" sign, SQL will add the two values together instead of concatenating them:

This returns the value of 3:

SELECT 1 + 2

This returns the value of 12:

SELECT CONCAT(1, 2)

In the first example, you will need to make sure you are converting integer values to a string, which could be quite cumbersome.

To read more on the CONCAT() function, go here: SQL CONCAT