Common Usage Flow-Control in Stored Procedure

In this section, we will cover all basic flow-control statement which can be used in stored procedure. There are two most common usage of flow-control which are conditional execution and loop.

Conditional execution with IF-THEN-ELSE and CASE statement

The IF-THEN-ELSE statement is used to evaluate the value of boolean expression; if the value is True it execute the block code that follows it otherwise it will execute the block code that follows by ELSE.The ELSE part is optional in the statement.

Here is an stored procedure which finds the maximum value between two integers using IF-THEN-ELSE statement. The example is easy just for demonstration.

CREATE PROCEDURE FindMax
	@v1 INT,
	@v2 INT,
	@m INT OUTPUT
AS
BEGIN
	IF @v1 > @v2
		SET @m = @v1
	ELSE 
		SET @m = @v2
END

In complex cases, we can use CASE statement instead of IF-THEN-ELSE statement. CASE statement evaluates a list of boolean expression and returns one of multiple possible result expressions. CASE statement is similar to the swith-case statement in modern programming language such as C# or Java. Here is the example of using CASE statement to display salary level of employee. We have employee table

employee_id  name      salary 
-----------  --------  -------
          1  jack      3000.00
          2  mary      2500.00
          3  newcomer  2000.00
          4  anna      2800.00
          5  Tom       2700.00
          6  foo       4700.00

And here is the example

CREATE PROCEDURE DisplaySalaryLevel
AS 
BEGIN
	SELECT  employeeId, name,salary, salary_level =
	CASE salary
		WHEN salary < 1000 
			THEN 'very low'
		WHEN salary > 1000 AND salary < 2000 
			THEN 'low'
		WHEN salary > 2000 AND salary < 4000 
			THEN 'average'
		WHEN salary > 4000 AND salary < 10000 
			THEN 'high'
		WHEN salary > 10000 
			THEN 'very high'
	END
	FROM Production.Product
END

Looping with WHILE statement

Since T-SQL is fourth generation language and designed to operate with sets of data so it is also possible to write the code to loop through the record set and perform operations on a single record. It will cause the performance of the server but in some cases it is necessary to use it. Here we will you an example of using WHILE loop statement to calculate the factorial of an integer number just for demonstration.

CREATE PROCEDURE Cal_Factorial
  @inyN INT,
  @intFactorial BIGINT OUTPUT
AS
BEGIN 	
	SET @intFactorial = 1

	WHILE @inyN > 1
	BEGIN
		SET @intFactorial = @intFactorial * @inyN
		SET @inyN = @inyN - 1
	END
END

In the real world database programming you will need to use the conditional statement to make you stored procedure. Next we will show you how to modify stored procedure using ALTER and DROP statement