How to use IF-ELSE in SQL Server Stored Procedure

I will explain how to use IF-ELSE in SQL server stored procedure in this SQL Server tutorial.

First, I will introduce the syntax of the IF-ELSE in SQL Server. Then, with an example, you will learn how to use the if-else in the stored procedure.

You will create a stored procedure containing the if-else statement and execute the sql statement of the stored procedure based on the condition.

Table of Contents

IF-ELSE in SQL Server Stored Procedure

To know how to use IF-ELSE in an SQL Server stored procedure, first, you must understand the if-else statement in SQL Server. This statement is used for conditional logic.

The syntax of the if-else is given below.

IF condition
	BEGIN
    	--  Write a SQL statemetn that is executed when condtion is true
	END
ELSE
	BEGIN
    	-- Write a SQL statemetn that is executed when condtion is false
	END

Where,

IF condition: Here, IF is the keyword followed by the condition, which can be any expression that must evaluate to a true or false value (or boolean value).
BEGIN and END: If the condition is true, then this section of IF is executed; otherwise, the ELSE section of the BEGIN…END block is executed. Remember, this section contains the SQL statement, query, or operation you want to execute based on the condition.

This is all about how you can use the if-else in the SQL Server, next let’s take an example where we will create stored procedure which contains the if-else statement.

I have the SalesTransactions table in the database’s Sales scheme, shown below.

IF-ELSE in SQL Server Stored Procedure SalesTransactions Table

Look at the data of the SalesTransactions in the above picture,

So here you have to create a stored procedure that takes the TransactionID as input, checks if the SaleAmount for the give transaction is greater than $100, if it is, the procedure computes the total amount including tax.

If the transaction is not greater than $100, it simply returns the SaleAmount without tax.

For that, you can look at the stored procedure ComputeTotalAmount.

CREATE PROCEDURE CalculateTotalAmount
	@TransactionID INT
AS
BEGIN
	DECLARE @SaleAmount DECIMAL(10, 2);
	DECLARE @TaxRate DECIMAL(5, 2);
	DECLARE @TotalAmount DECIMAL(10, 2);

	SELECT @SaleAmount = SaleAmount, @TaxRate = TaxRate
	FROM Sales.SalesTransactions
	WHERE TransactionID = @TransactionID

	IF @SaleAmount > 100
	BEGIN
    	SET @TotalAmount = @SaleAmount + (@SaleAmount * @TaxRate)
    	PRINT 'Total Amount including tax: ' + CAST(@TotalAmount AS VARCHAR)
	END
	ELSE
	BEGIN
    	PRINT 'Total Amount (no tax applied): ' + CAST(@SaleAmount AS VARCHAR)
	END
END

Creating IF-ELSE in SQL Server Stored Procedure

In the above-stored procedure, accept the TransactionID of tye INT as input and declare the three variables @SaleAmount, @TaxRate and @TotalAmount of type Decimal.

After that, a statement SELECT @SaleAmount = SaleAmount, @TaxRate = TaxRate FROM Sales.SalesTransactions WHERE TransactionID = @TransactionID retrieves the SaleAmount and TaxRat for the given TransactionID from the Sales.SalesTransactions table.

Then, the IF-ELSE checks if the SaleAmount is greater than $100. If true, it computes the total amount, including tax, and prints it. Otherwise, it prints the sale amount without adding tax in case of falsity.

Let’s check the stored procedure with TransactionID. For example, input the TransactionID as 1 to CalculateTotalAmount, as shown below.

EXEC CalculateTotalAmount 1;

Look at the output. When you pass the TransactionID as 1, the procedure returns the total amount as 100 without tax applied to it. That is because the IF condition becomes false in the Stored procedure, and it simply executes the ESLE block.

Now, let’s pass the different transaction IDs.

EXEC CalculateTotalAmount 2;

Testing IF-ELSE in SQL Server Stored Procedure

This time, when you pass the transaction as 2 it returns the total amount including the tax here IF condition becomes true because if you check the sale amount of given transaction ID in the database, which is $200.

The above is a simple example of an if-else statement in a stored procedure; you can even nest the if-else statement in the stored procedure.

Conclusion

In this SQL Server tutorial, you learned how to use IF-ELSE in SQL server stored procedure in this SQL Server tutorial.

Also learned the syntax of IF-ELSE statement, For example you created the CalculateTotalAmount stored procedure based on the provided TransactionID it executes the if-else block with procedure.