Cool Ways I've Used SQL Part 2 - IF/ELSE
Time for another edition of “Cool Ways I’ve Used SQL.”
If you don’t give a damn about data, this one ain’t for you. But if data and SQL interest you in any way, you’re in the right place.
Today, I’m talking about IF/ELSE statements.
If you know any programming languages (even at a basic level), you know about IF/ELSE statements. They’re basic control-of-flow logic that allows you to execute certain code blocks if a given condition is met.
Not everyone knows that SQL supports IF/ELSE statements. These statements function the same way in SQL as they do in programming languages. I’ve only worked with two RDBMSs (SQL Server and PostgreSQL), but all dialects and databases support this feature.
IF/ELSE statements seem similar to case statements, and that’s true. Both allow you to manipulate data with conditionals. However, their difference lies in their granularity. Case statements (and IIF() functions in some dialects) execute at the row level. So, they only manipulate records in the context of the individual row evaluated. IF/ELSE, however, runs at the script or procedural block level. They’re only used in procedural scripts—stored procedures, DB management scripts, triggers, functions, and ETL scripts, to name a few. Many people don’t realize that IF/ELSE exists in SQL because of this.
Unless your data role has you managing a DB and/or performing ETL transformations, you’ll likely not need to know IF/ELSE. But that doesn’t mean it’s not good to know how they work, nor does it mean they’re not fun to play around with.
Since I build complex SSRS reports, help maintain my company’s databases, and develop stored procedures for ETL or application needs, I’m blessed with getting to use IF/ELSE often. Here are two simple examples of how I’d use them.
The first is for SSRS reports.
The reports I build often contain numerous parameters to allow users to filter and slice data. This happens with all sales reports I generate. When automating report delivery for salespeople, we only want them to see data for their accounts. It wouldn’t be good if they see the numbers for all salespeople. However, we still need an “ALL SALESPERSONS” option in our parameters so that sales and brand managers can see a comprehensive view of salesperson performance.
So, the SQL I write for a report may look something like this:
1select *
2into #Sales
3from dbo.Sales
4
5IF (@SalesPersonId <> 'ALL')
6BEGIN
7 delete from #Sales
8 where SalesPersonId <> @SalesPersonId
9END
10
11select *
12from #SalesSQL in SSRS reports differs slightly from your usual T-SQL due to the way SSRS handles parameters. That’s why you see ‘@’ above.
If @SalesPersonId equals ‘ALL’, the IF statement is not executed, and the query returns sales data for all salespeople. But if @SalesPersonId is set to a specific salesperson’s ID, the IF statement executes and removes all rows not related to the salesperson. This ensures the final select statement only returns data for the specified salesperson.
The second example I have concerns DB Restore scripts.
I don’t work with big data, so the databases I use are small enough to fit on my local machine. This is useful for SQL development and testing purposes. Testing in the production environment is never smart, but I’m guilty of doing it sometimes. I can also test in our staging environments, but sometimes doing this breaks our software applications using the same DB.
So, I often do my SQL development and testing with a recent DB backup on my local laptop. This means I frequently have to restore my local database to have fresh, near-production data for testing.
But there’s an issue. Remember, I still have access to our staging and production environments. And the last thing I want to do is accidentally execute my restore script in one of those environments. That would be catastrophic and impact the development of our software engineers. Or, even worse, I could accidentally log in with credentials containing restore privileges in production and overwrite our production data. That would get me in big trouble and possibly fired. Luckily, the production credentials I have don’t contain database restore privileges, but you never know what could happen.
To overcome this obstacle, my restore scripts have IF/ELSE statements to ensure the actual restore logic only executes if the current server is my localhost. The SQL looks something like this:
1IF (@@SERVERNAME <> 'localhost\servername')
2BEGIN
3 PRINT 'STOP!!! THIS IS NOT YOUR LOCALHOST'
4 RETURN
5END
6ELSE
7BEGIN
8 --Execute the DB restore logic
9END