Variables in SQL Server

• Local variables: Used within stored procedures, functions, or batches to hold temporary data.
• Global variables: Predefined by SQL Server and provide information about the system state (e.g., server name, row counts, errors).
• Dynamic SQL: Allows execution of SQL code dynamically based on variable input.

1. Local Variables:

Local variables are used to store data temporarily in a session, procedure, or batch. You can define and manipulate local variables within a batch, function, or stored procedure.

• Batch: A block of code that is highlighted and executed together.

• Function: Variables can be used inside user-defined functions.

• Stored Procedure: Variables are commonly used within stored procedures to hold temporary data.

• To define a variable:

  DECLARE @x INT; -- Declares a variable @x of type INT. The initial value is NULL.
  

• To assign a value to a variable:

  • Using SET:

    SET @x = 10; -- Assigns the value 10 to the variable @x.
    

  • Using SELECT:

    SELECT @x = 100; --Assigns the value 100 to the variable @x.
    

  • From a query result:

    SELECT @x = (SELECT AVG(Salary) FROM Employees WHERE EmployeeID = 1);
    -- Fetches the average salary of EmployeeID 1 and assigns it to @x.
    

  • During an UPDATE statement:

    UPDATE Employees
    SET EmployeeName = 'Omar', @x = Salary
    WHERE EmployeeID = 9;
    -- Updates the EmployeeName of EmployeeID 9 to 'Omar' and assigns their Salary to @x.
    

• To print the value of a variable:

  SELECT @x; -- Displays the value stored in @x.
  

Examples of Local Variables:

1. Calculate and assign the average salary to a variable:

   USE CompanyDB;
   -- Switch to the CompanyDB database.
   -- ⇒ 1, 2, and 3 must excute together
   1⇒ DECLARE @x INT;  
   -- Declare a variable @x of type INT.
   
   2⇒ SELECT @x = AVG(Salary) FROM Employees;
   --Calculate the average salary of all employees and assign it to @x.
   
   3⇒ SELECT @x;
   -- Display the value of @x.
   

2. Handling cases where a query returns no result: If a query doesn't return a result, the variable retains its previous value:

   DECLARE @x INT = 100;
   -- Declare @x with an initial value of 100.
   
   SELECT @x = Salary FROM Employees WHERE EmployeeID = 121312312;
   -- No Employee with this ID, so the variable remains 100.
   
   SELECT @x;
   -- Output will still be 100.
   

3. Handling multiple row results: When a query returns multiple rows, the variable is assigned the last value:

   DECLARE @x INT;
   -- Declare @x without an initial value.
   
   SELECT @x = Salary FROM Employees WHERE EmployeeID > 1;
   -- If multiple employees are returned, @x will hold the last Salary.
   
   SELECT @x;
   -- Outputs the last salary from the result set.
   

4. Using multiple variables:

   DECLARE @EmpSalary INT, @EmpName VARCHAR(50);
   -- Declare two variables: @x (int) and @y (string).
   
   SELECT @EmpSalary = Salary, @EmpName = EmployeeName FROM Employees WHERE EmployeeID = 1;
   -- Assign the salary and first name of EmployeeID 1 to @x and @y, respectively.
   
   SELECT @EmpSalary, @EmpName;
   -- Display the values of @x and @y.
   

5. Using variables in an UPDATE statement:

   DECLARE @DeptID INT;
   -- Declare a variable @x.
   
   UPDATE Employees
   SET EmployeeName = 'Gazal', @DeptID = DepartmentID
   WHERE EmployeeID = 1;
   -- Update the EmployeeName of EmployeeID 1 and assign their DepartmentID to @x.
   
   SELECT @DeptID;
   -- Display the value of @x.
   

6. Using table variables: Table variables can be used to temporarily store multiple rows in memory:

   DECLARE @SalaryTable TABLE (Salary INT);
   -- Declare a table variable @SalaryTable with a Salary column of type INT.
   
   INSERT INTO @SalaryTable
   SELECT Salary FROM Employees WHERE EmployeeID > 1;
   -- Insert the salaries of employees with ID > 1 into the table variable.
   
   SELECT * FROM @SalaryTable;
   -- Select all rows from the table variable @SalaryTable.