Conditional Functions

In this video segment, we will discuss some conditional functions built into SQL Server. Conditional functions allow us to return results based on a true or false condition. So let’s take a look at our demonstration.

In the query editor window, you will notice I’ve typed out the code to select all the records from the Sales.SalesOrderDetail table. You should also notice that I’ve already executed the query and returned a result set. The two columns we will be working with for this demonstration is a unit price field and a special offer ID field.

First, let’s take a look at the unit price field specifically. So we’ll go ahead and type in UnitPrice. And I will execute this code so we can see a list of the prices of our products. I also want to have a list that shows me that if the unit price is greater than, if the unit price is greater than 500, greater than 500, then I want to list that as a bicycle, bicycle. Now if the unit price is greater than 500 or if that statement is not true, then I will want to put in accessories, that that value is an accessory, accessories.

So again, the statement, the I if statement or the IF conditional function, is saying that if the unit price is greater than 500, if that’s true, put the word bicycle. If it is false, use the word accessories. So let’s execute our code. And you could see that any value that is greater than 500 had a bicycle in the result. Anything less than 500 placed an accessories into the result list.

Next, let us take a look at the special offer ID field. So I’ll go ahead and erase what we’ve already typed here. Go ahead and get that out of the way. And we’ll type in SpecialOfferID. Now when I execute this code, you can see there’s a list of the special offer IDs. There’s actually 12 different values. To see these values, to see the unique values, I can use the word distinct. Distinct allows me to see unique or specific values. So if I click Execute, you see that there are 12 different values.

Next, we’re going to use the Choose conditional function to display the results. So I’ll type in CHOOSE and if the special offer ID, SpecialOfferID, is a one, then I want to display that there was no discount for that listing. If the value is a two, then I will place that there was a seasonal, seasonal discount. And I will list on the next line a third option that if the value was a three, then there was a volume discount.

So the CHOOSE statement allows me to display results based off where they are in a positional list. So let me go ahead and execute. And we could see that the values that I did put in, the one has no discount, the two had a seasonal discount, and three had a volume discount. If I didn’t include anything in the CHOOSE list, then it came back as a null value. So in this video segment, we have discussed how to use conditional functions.