hosting tips

SQL Server Tutorial: How to Use a Cursor in SQL?

Suzan Kara

Here’s how to use a cursor in SQL with a practical example,

  1. First, we need to understand what a cursor is and why it might be useful.
  2. Then, we’ll create a sample table and populate it with data.
  3. We’ll define the cursor and declare variables to store the data.
  4. We’ll open the cursor and fetch data from it.
  5. Finally, we’ll close and deallocate the cursor.

Cursors in SQL

  • Cursors allow us to iterate through a result set one row at a time.
  • They are useful when we need to perform multiple operations on each row of a result set.
  • Cursors can be explicit (programmer-defined) or implicit (automatically created).
  • Explicit cursors give us more control over the iteration process.

Let’s create a sample scenario where we have a table of employees, and we want to update their salaries based on certain criteria.

Create a sample table

-- Create a sample table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

Insert sample data

-- Insert sample data
INSERT INTO Employees (EmployeeID, Name, Department, Salary)
VALUES
    (1, 'John Doe', 'IT', 50000.00),
    (2, 'Jane Smith', 'HR', 45000.00),
    (3, 'Bob Johnson', 'Finance', 55000.00),
    (4, 'Alice Brown', 'Marketing', 40000.00);

Let’s See the Result by running this script.

SELECT * FROM [dbo].[Employees]

Output

Write the script below and run it.

-- Open the cursor
OPEN EmployeeCursor;

-- Fetch the first row
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @Name, @Department, @Salary;

-- Loop through the rows
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Perform operations on the current row
    IF @Department IN ('IT', 'Finance')
        SET @Salary = @Salary * 1.1; -- Increase salary by 10% for IT and Finance departments

    -- Print the updated information
    PRINT 'Employee ID: ' + CAST(@EmployeeID AS VARCHAR(10)) +
          ', Name: ' + @Name +
          ', Department: ' + @Department +
          ', New Salary: ' + CAST(@Salary AS VARCHAR(10));

    -- Move to the next row
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @Name, @Department, @Salary;
END

-- Close and deallocate the cursor
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

In this script, we update employees’ salaries by 10% for IT and Finance.

Output

SQL Server Hosting Recommendation

HostForLIFEASP.NET is a large provider that serves millions of websites and is constantly growing.In fact, the fast speed of HostForLIFEASP.NET is not only achieved by their great web servers and advanced data center infrastructures, but also by some special technologies and services that are listed in the following.

HostForLIFEASP.NET Network Operations Center (NOC) is built right in the center of their support facilities and is staffed 24/7/365 by certified systems administrators and network engineers. The 21 screens that make up their NOC monitor everything from network traffic and performance to power, temperature and security systems to services, applications, known vulnerabilities and RAID array status on customer servers.

HostForLIFEASP.NET data centers are built upon a unique pod design concept, making them functionally independent with distinct and redundant resources, and fully integrated through their revolutionary network architecture. You can have direct control over your system in any data center and full access to all of their back-end services—all fully automated and on demand.

hostforlifebanner