Common Usage Flow-Control in Stored Procedure
In this section, we will cover all basic flow-control statements which can be used in stored procedures. 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 thestatement.
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 other programming languages such as C/C++, C# or Java. Here is an example of using CASE statement to display salary level of employee. We have employee table data as follows:
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 stored procedure 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 therefore it is also possible to write the code to loop through the record set and perform operations on a single record. Using loop will cause the performance of the server but in some cases it is necessary to use this feature. Here we will you an example of using WHILE loop statement to calculate the factorial of an integer number.
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 statements to write own your stored procedures. Next we will show you how to modify stored procedure using ALTER and DROP statement