Declaring Variables
In SQL, variables are used to store temporary data that can be utilized in queries, loops, and other SQL statements. They are often used in stored procedures, functions, or scripts.
Syntax:
DECLARE @variable_name data_type;
Example:
DECLARE @employeeCount INT;
SET @employeeCount = (SELECT COUNT(*) FROM employees);
In this example, @employeeCount is an integer variable that stores the total number of employees in the employees table.
Using WHILE Loops
The WHILE loop in SQL is used to repeatedly execute a block of statements as long as a specified condition is true. This is useful for performing repetitive tasks or iterating over a set of values.
Syntax:
WHILE condition
BEGIN
-- statements to be executed
END;
Example:
DECLARE @num INT = 5;
DECLARE @factorial INT = 1;
WHILE @num > 1
BEGIN
SET @factorial = @factorial * @num;
SET @num = @num - 1;
END;
SELECT @factorial AS Factorial;
In this example:
- A variable @num is initialized to 5, and @factorial is initialized to 1.
- The loop runs as long as @num is greater than 1, multiplying @factorial by @num and decrementing @num by 1 with each iteration.
- The result is the factorial of the number, which is then selected.
