DECLARE @Threshold DECIMAL(10, 2) = 1000;
IF EXISTS (
SELECT *
FROM Production.Product
WHERE Price > @Threshold
BEGIN
PRINT 'There are products with prices greater than $' + CAST(@Threshold AS VARCHAR);
This query will check if there are items in the table that cost more than $1000 and let you know by printing ‘There are products with prices greater than $1000’ if the condition is true:
BEGIN
PRINT 'There are products with prices greater than $' + CAST(@Threshold AS VARCHAR);
BEGIN
PRINT 'No products with prices greater than $' + CAST(@Threshold AS VARCHAR);
As a result, SQL Server has no problem notifying us in both cases: when the specified condition is true and when it is false.
The IIF function in SQL Server is a handy tool that simplifies conditional queries. It provides a concise way to express IF-THEN-ELSE logic in a single function call instead of writing separate statements. To widen our knowledge, let us explore the IIF function, its syntax, and how it simplifies conditional expressions:
IIF(condition, true_value, false_value);
In the syntax above, the condition is the expression to be evaluated. If the condition happens to be true, the true_value is returned; otherwise, the false_value.
The primary difference between IF and IIF in SQL lies in their usage and syntax. The IF statement is used for control flow in procedural code and requires separate blocks of code for each condition. On the other hand, the IIF function is used within SQL expressions and returns a value directly based on the condition.
Examples of IIF Function
Now, we will illustrate the usage of the IIF function:
1. Return Yes or No based on a condition:
SELECT
ProductId,
IIF(Qty > 0, 'Yes', 'No') AS in_stock
FROM Production.Stock;
With this query, we can determine the availability of a specific item in stock. It checks the quantity (Qty) for each product, and if the quantity is greater than 0, it assigns the value Yes to the in_stock column. On the contrary, if the quantity is 0, the same column gets the No value. The query provides a result set showing the ID of the product and the corresponding availability status in the table:
BEGIN
-- Inner IF-THEN block
PRINT 'There are orders with a total amount exceeding $5000.';
BEGIN
-- Inner IF-THEN alternative block
PRINT 'No orders with a total amount exceeding $5000 found.';
BEGIN
-- Outer IF-THEN alternative block
PRINT 'No high-value orders found.';
In this example, we have an outer IF-THEN block that checks if there are any orders in the Orders table with TotalAmount greater than 1000. If such orders exist, the statement inside the outer IF-THEN block will be executed, printing High-value orders found.
Within the outer IF-THEN block, there is a nested IF-THEN block that checks if there are any orders with TotalAmount exceeding 5000. If there are, the statement inside the inner IF-THEN block will be executed, and you will see the following message: There are orders with a total amount exceeding $5000. If no orders exceed that amount, this message will be printed instead: No orders with a total amount exceeding $5000 found.
Finally, if there are no orders with TotalAmount greater than 1000, the statement inside the outer IF-THEN alternative block will be executed, printing No high-value orders found.
DECLARE @OrderId INT;
DECLARE @TotalAmount DECIMAL(10, 2);
DECLARE @PaymentStatus VARCHAR(20);
-- Check the TotalAmount and update the PaymentStatus accordingly
IF @TotalAmount > 1000
BEGIN
-- Code block for high-value orders
SET @PaymentStatus = 'Pending';
BEGIN
-- Code block for orders below the high-value threshold
SET @PaymentStatus = 'Approved';
-- Update the PaymentStatus in the table
UPDATE BicycleStoreDemo.Sales.Orders
SET PaymentStatus = @PaymentStatus
WHERE OrderId = @OrderId;
-- Commit the transaction
COMMIT;
-- Print a message based on the PaymentStatus
IF @PaymentStatus = 'Pending'
BEGIN
PRINT 'Payment for the high-value order has been set to Pending.';
BEGIN
PRINT 'Payment for the order has been successfully processed.';
This script represents a transaction involving a payment process for the Orders table. The IF-THEN statement is used to check the TotalAmount value. If it is greater than 1000, the script sets the PaymentStatus variable to Pending. Otherwise, for orders below the high-value threshold, it sets the PaymentStatus to Approved.
After determining the PaymentStatus, the script updates the Orders table with the corresponding status for the given OrderId.
Then, the script commits the transaction, ensuring that all the changes made within the transaction are permanently applied. It then prints a message based on payment status, confirming it for the order.
To sum up, understanding T-SQL control-of-flow statements, specifically SQL Server IF-THEN statements, along with related common pitfalls and best practices, is crucial for effective database management. To experience the power of IF-THEN statements and explore further features, download a 30-day free trial version of dbForge Studio for SQL Server. This powerful tool provides a user-friendly interface to test and optimize your SQL code, enabling you to harness the potential of control-of-flow statements and more.
If you have been looking for a comprehensive guide on the MySQL IF statement, you will be delighted to find it on our blog.
Founded in 1997, Devart is currently one of the leading developers of database management software, ALM solutions and data providers for most popular database servers.
© 2024 Devart. All rights reserved.
