How to Use a Cursor in SQL?

 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)
);

SQL

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);

SQL

Let's See the Result by running this script.

SELECT * FROM [dbo].[Employees]

SQL

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;

SQL

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

Output

Summary

This example demonstrates how to use a cursor in SQL Server to iterate through a result set and perform operations on each row. Here's a breakdown of what the code does:

1. We create a sample table of Employees and insert some data.
2. We declare variables to hold the data from each row.
3. We define the cursor based on our SELECT statement.
4. We open the cursor and fetch the first row.
5. We use a WHILE loop to continue fetching rows until there are no more rows to fetch.
6. Inside the loop, we perform operations on the current row (increasing salaries for certain departments).
7. We print the updated information for each row.
8. Finally, we close and deallocate the cursor.

DATETRUNC New T-SQL Enhancements in SQL Server

 In this article, we'll explore how the new DATETRUNC function works, look at some practical use cases, and examine how it differs from traditional data manipulation methods. This article is written for SQL developers, DBAs, and data engineers who are looking to understand how to leverage this new functionality in SQL Server 2022.

Understanding DATETRUNC

The DATETRUNC function allows you to truncate a datetime or datetime2 value to a specified date part. This means you can strip the time portion or reduce the precision of a date down to a specific unit such as a year, quarter, month, day, hour, minute, or second. The DATETRUNC function is a more streamlined and expressive way to handle date truncation compared to the traditional methods involving DATEADD, DATEDIFF, and CONVERT.

Syntax

DATETRUNC ( datepart, expression )

--datepart: The part of the date to truncate. This can be year, quarter, month, day, hour, minute, second, etc.
--expression: The datetime, smalldatetime, date, or datetime2 expression to truncate.

SQL

Examples

Here’s an example of truncating a DateTime value to different date parts using DATETRUNC.

USE [AdventureWorks2022]
GO

DECLARE @date datetime2 = '2024-10-08 13:45:30.123'

-- Truncate to year
SELECT DATETRUNC(year, @date) AS TruncatedToYear

-- Truncate to month
SELECT DATETRUNC(month, @date) AS TruncatedToMonth

-- Truncate to day
SELECT DATETRUNC(day, @date) AS TruncatedToDay

-- Truncate to hour
SELECT DATETRUNC(hour, @date) AS TruncatedToHour

-- Truncate to minute
SELECT DATETRUNC(minute, @date) AS TruncatedToMinute

-- Truncate to second
SELECT DATETRUNC(second, @date) AS TruncatedToSecond

SQL

Output

Use Cases

DATETRUNC offers several advantages for common date truncation use cases in SQL Server, making it easier to,

• Group by a Specific Date Part: You can use DATETRUNC to group data by a specific date part (e.g., by month, day, or hour).
• Remove Time from Datetime Values: Often, developers need to work with only the date part, ignoring the time part.
• Aggregate Data at Different Levels: DATETRUNC makes it easy to aggregate data at different levels of granularity such as hourly, daily, weekly, or monthly.
• Date Range Filtering: DATETRUNC helps simplify date-based filtering where you need to match specific parts of a date.

Comparing DATETRUNC with Traditional Methods

Before the introduction of DATETRUNC, SQL Server developers typically used a combination of DATEADD, DATEDIFF, and CONVERT to achieve similar results. Let’s compare DATETRUNC with traditional approaches.

Truncating to Day

The DATETRUNC approach is much simpler and more intuitive compared to the CAST and CONVERT methods. Here’s how you might truncate a datetime value to the day using traditional methods:

USE [AdventureWorks2022]
GO

DECLARE @date datetime2 = '2024-10-08 13:45:30.123';

-- Traditional method
SELECT CAST(CONVERT(date, @date) AS datetime) AS TruncatedToDay;

-- Using DATETRUNC
SELECT DATETRUNC(day, @date) AS TruncatedToDay;

SQL

Output

Truncating to Month and Year

USE [AdventureWorks2022]
GO

DECLARE @date datetime2 = '2024-10-08 13:45:30.123';

-- Truncating to Month
-- Traditional method
SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0) AS TruncatedToMonth;

-- Using DATETRUNC
SELECT DATETRUNC(month, @date) AS TruncatedToMonth;

-- Truncating to Year
-- Traditional method
SELECT DATEADD(year, DATEDIFF(year, 0, @date), 0) AS TruncatedToYear;

-- Using DATETRUNC
SELECT DATETRUNC(year, @date) AS TruncatedToYear;

SQL

Conclusion

By replacing more complex combinations of DATEADD and DATEDIFF with a single, readable function, DATETRUNC helps reduce query complexity, improve maintainability, and streamline date-based calculations.

As a SQL professional, mastering DATETRUNC will enhance your ability to write clean and efficient T-SQL code, making it easier to work with DateTime data in SQL Server 2022. Whether you're aggregating sales by month or grouping website traffic by hour, DATETRUNC can be a valuable tool in your SQL library.