Go to Top

Using IF and ELSE in MSSQL

This guide explains how to use IF and ELSE in MSSQL.

IF and ELSE statements can be very usefull in MSSQL in alot of situations especially when you need to verify something prior to deleting or changing the record.

It works in a very similar way to php.

In PHP it is:

IF ($condition) {
echo 'condition is true'
}
ELSE {
echo 'condition is false'
}

In SQL you use it with the same structure in mind. For example:

IF (SELECT count(*) FROM people WHERE status = '0') > 1
UPDATE people
SET status = 'archive'
WHERE status = '0'
ELSE
SELECT 'No people with status 0'

The above query selects a count of people who’s status is 0. If there are people who’s status is 0 it will change their status to archive, else if there are no people who’s status is 0 it will return a message saying there are no people who’s status is 0.

Rather than just going in and changing the record straight away the above query allows you to perform validation to ensure actual records exist before performing the delete operation.

The above example may not be a great senario where you would use this query however it should help you grasp the basics of what it does and how to write your own query. More likely you would be checking records accross multiple databases and wanting to verify data prior to changing anything that could cause issues.

To expand on the query above you could have a set of queries executing in a row which you may need to stop during execution if a condition is false. To do this you can use another set of SQL commands called BEGIN, RETURN and END.

I’ll use the example above again but add some lines to it to show you how it would work.

IF (SELECT count(*) FROM people WHERE status = '0') > 1
UPDATE people
SET status = 'archive'
WHERE status = '0'
ELSE
BEGIN
SELECT 'No people with status 0'
RETURN
END

UPDATE account
SET accountstatus = 'archive'
WHERE accountid IN (SELECT id FROM people WHERE status = 'archive')

The query above still performs exactly the same operation as our first query however the added lines of code perform an important extra function.

If there were any people found with the status of 0 the first query will update all those to a status of archive. After this it will go ahead and perform the second query where it updates any accounts to a status of archive where the accountid appears in the id of people who’s status has just been set to archive.

However, what BEGIN, RETURN and END will do is stop executing the query if there are no people with a status of 0, this means the second part of the query won’t run and this will save on processing time, after all, if the first query doesnt update anything there is absolutely no need for the second query to even run.

As i said earlier this is a very basic use of the query to help you understand the basics of how they work and allow you to adapt it for yourself.

If you have any questions feel free to ask below.

, , ,

Leave a Reply

Your email address will not be published. Required fields are marked *


4 + four =