Stopbyte

How to correctly do IF..ELSE statement in a Stored Procedure in SQL?

Database Development SQL Server
,
#1

How to correctly do IF…ELSE statement in a Stored Procedure in SQL?

1 Like
#2

According to Microsoft’s official documentation, IF…ELSE recommended syntax is quite simple.

This is the if…else syntax:

IF Boolean_expression   
     { sql_statement | statement_block }   
[ ELSE   
     { sql_statement | statement_block } ]

The ELSE block within [] is optional.

This is a sample Transact-SQL Stored Procedure, that’s common for e-commerce use: Getting a list of products filtered by an optional CategoryID column.

CREATE PROCEDURE [dbo].[GetProducts]
	@CategoryID INT = -1
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM Categories WHERE CategoryID=@CategoryID)
		BEGIN
			SELECT * FROM Products
		END
	ELSE
		BEGIN
			SELECT * FROM Products WHERE CategoryID=@CategoryID
		END
END

The example above uses if…else, and checks if the given CategoryID exists in the Categories table using (NOT EXISTS statement), and then calls the appropriate SELECT statement.

you can change that sample to suit your needs :blush: