In SQL, you can declare and use a cursor to iterate over a result set, and you can also pass parameters to filter that result set dynamically. Below is an example that demonstrates how to accomplish this in a SQL environment like SQL Server. The concept is similar in other databases, but the syntax may vary slightly.
Example: Declaring and Using a Cursor with Parameters
Let's say you have a table Employees and you would like to filter the employees based on the department and minimum salary.
Step 1: Create Example Table and Data
For this example, let's assume we have the following table:
CREATE TABLE Employees (
EmployeeID INT,
Name NVARCHAR(100),
Department NVARCHAR(50),
Salary DECIMAL(10, 2)
);
INSERT INTO Employees (EmployeeID, Name, Department, Salary)
VALUES
(1, 'Alice', 'HR', 60000),
(2, 'Bob', 'IT', 75000),
(3, 'Charlie', 'IT', 50000),
(4, 'David', 'HR', 55000),
(5, 'Eve', 'Finance', 70000);
Step 2: Declare a Cursor with Parameters
Here's how you can declare a cursor that takes parameters to filter the result set dynamically:
DECLARE @Department NVARCHAR(50),
@MinSalary DECIMAL(10, 2);
-- Set parameters
SET @Department = 'IT';
SET @MinSalary = 60000;
DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, Name, Department, Salary
FROM Employees
WHERE Department = @Department AND Salary >= @MinSalary;
Step 3: Open the Cursor and Fetch Data
Now, you can open the cursor and fetch data from it:
OPEN EmployeeCursor;
DECLARE @EmployeeID INT,
@Name NVARCHAR(100),
@Dept NVARCHAR(50),
@Salary DECIMAL(10, 2);
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @Name, @Dept, @Salary;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Employee ID: ' + CAST(@EmployeeID AS NVARCHAR) +
', Name: ' + @Name +
', Department: ' + @Dept +
', Salary: ' + CAST(@Salary AS NVARCHAR);
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @Name, @Dept, @Salary;
END
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;
Explanation
1. Parameter Declaration: We declare variables @Department and @MinSalary to hold the filtering criteria.
2. Cursor Declaration: The cursor EmployeeCursor is declared with a SELECT statement that uses the parameters defined earlier.
3. Fetching Data: The cursor is opened, and data is fetched in a loop until there are no more rows to process (@@FETCH_STATUS becomes -1).
4. Clean Up: After processing, it's essential to close and deallocate the cursor to free up resources.
Note
- While cursors can be useful for certain tasks, they can also be inefficient for large data sets. Whenever possible, try to use set-based operations rather than row-by-row processing.
- The example uses SQL Server's syntax; if you are using a different SQL database, the cursor handling might differ slightly.