zlika's

The cursors in MS SQL Server are not the best way to manipulate the data, because they need more recourse. If you can escape the usage of cursors, do it. OK, the cursors give us a flexible way of manipulating the information. You can imagine a bundle of information, that we use for move through other information and modify it using the information of our bundle (puting the information in the WHERE clause).

Let’s say we need to modify a table containing user information. We need to mark those of them, who are registered more than a month (or 30 days) ago. Simultaneously, we need to write in another table only the modified users and the time of modification.

First we create tables, add keys and populate tables.

USE zlika
GO

-- Create Tables
CREATE TABLE Departments
(
	ID INT NOT NULL
	, DepartmentName VARCHAR(50)
)
GO

CREATE TABLE Person
(
	ID INT IDENTITY
	, FirstName VARCHAR(20)
	, LastName VARCHAR(50)
	, DepartmentID INT
	, RegisteredDate DATETIME
	, CheckStatus BIT
)
GO

CREATE TABLE Checked
(
	PersonID INT
	, CheckDate DATETIME
)
GO

-- Add Keys
ALTER TABLE Departments
ADD CONSTRAINT PK_DepartmentID PRIMARY KEY CLUSTERED
(
	ID ASC
)
GO

ALTER TABLE Person
WITH CHECK ADD CONSTRAINT
FK_Departments_DepartmentID FOREIGN KEY
(
	DepartmentID
)
REFERENCES Departments
(
	ID
)
GO

-- Populate Tables
INSERT INTO Departments (ID, DepartmentName) VALUES
(1, 'IT')
, (2, 'Administration')
, (3, 'Technical Support')
, (4, 'Accounting')
, (5, 'Marketing')
GO

INSERT INTO Person (Firstname, LastName, DepartmentID, RegisteredDate) VALUES
('First', 'Smith', 5, '2010-02-01')
, ('Second', 'Brown', 4, '2010-02-02')
, ('Third', 'Marchal', 3, '2010-02-03')
, ('Fourth', 'Jameson', 2, '2010-02-04')
, ('Fifth', 'Anderson', 1, '2010-02-05')
, ('Sixth', 'Cameron', 1, '2010-02-06')
, ('Seventh', 'Blake', 2, '2010-02-07')
, ('Eight', 'Robert', 3, '2010-02-08')
, ('Ninth', 'Jameson', 4, '2010-02-09')
, ('Tenth', 'Wiliams', 5, '2010-02-10')
GO

-- Preview Tables
SELECT
	*

FROM
	dbo.Person AS P INNER JOIN dbo.Departments AS D
		ON P.DepartmentID = D.ID
GO

Create and populate tables

Let’s say we need to mark only the users of IT, Technical Support and Marketing departments who are registered 28 days ago.

USE zlika
GO

SELECT
	P.FirstName
	, P.LastName
	, D.DepartmentName
	, CONVERT(CHAR(10), P.RegisteredDate, 103) AS RegisteredDate

FROM
	dbo.Person AS P INNER JOIN dbo.Departments AS D
		ON P.DepartmentID = D.ID
GO

Select all persons

The information that we’re going to use as filter (in the WHERE clause) is PersonID, RegisteredDate from Person table. That’s the query that is going to populate the cursor.

USE zlika
GO

SELECT
	P.ID
	, P.RegisteredDate

FROM
	dbo.Person AS P INNER JOIN dbo.Departments AS D
		ON P.DepartmentID = D.ID

WHERE
	D.DepartmentName IN ('IT', 'Technical Support', 'Marketing')
	AND P.RegisteredDate < DATEDIFF(DAY, 28, GETDATE()) -- 2010-02-06
GO

Populate Cursor Statement

The work we need to do is between BEGIN and END keywords. First we mark the status as ‘checked’ (1), then we add the PersonID and current date in another table (Checked).

USE zlika
GO

-- Execute the UPDATE Statement
UPDATE Person
SET CheckStatus = 1
WHERE ID IN (1, 3, 5)

-- Execute the INSERT Statement
INSERT INTO Checked (PersonID, CheckDate) VALUES
(1, GETDATE())
, (3, GETDATE())
, (5, GETDATE())

OK, here’s the guy who do the real work – the cursor itself. We store the content of the cursor in a variables and we execute the two steps for every row of it. The variables are our filters – we use them in the WHERE clause.

USE zlika
GO

-- Declare the variables
DECLARE @PersonID INT
DECLARE @Date DATETIME = DATEDIFF(DAY, 28, GETDATE())

-- Declare the cursor
DECLARE Update_Person_Cursor CURSOR FOR

-- Populate the cursor
SELECT
	P.ID
	, P.RegisteredDate

FROM
	dbo.Person AS P INNER JOIN dbo.Departments AS D
		ON P.DepartmentID = D.ID

WHERE
	D.DepartmentName IN ('IT', 'Technical Support', 'Marketing')
	AND P.RegisteredDate < @Date
	AND P.CheckStatus IS NULL

-- Open the cursor to work with it
OPEN Update_Person_Cursor

-- Select The first row into the variables (@PesronID = 1, @Date = 2010-02-01)
FETCH NEXT FROM Update_Person_Cursor INTO
	@PersonID
	, @Date

-- While there are no more rows in the cursor...
WHILE @@FETCH_STATUS = 0

-- Do the two steps!
BEGIN
	-- Execute the UPDATE statement
	UPDATE Person
	SET CheckStatus = 1
	WHERE ID = @PersonID

	-- Execute the INSERT statement
	INSERT INTO Checked (PersonID, CheckDate) VALUES
	(@PersonID, GETDATE())

	-- Select The second row into the variables (@PesronID = 3, @Date = 2010-02-03)
	FETCH NEXT FROM Update_Person_Cursor INTO
		@PersonID
		, @Date
END

-- Close The cursor
CLOSE Update_Person_Cursor

-- Kill the cursor
DEALLOCATE Update_Person_Cursor
GO

The records in the ‘Checked’ table are those we needed.

USE zlika
GO

SELECT * FROM Checked

Content of 'Checked' table

…and the status in the ‘Person’ table is changed to ’1′.

USE zlika
GO

SELECT
	*

FROM
	dbo.Person AS P INNER JOIN dbo.Departments AS D
		ON P.DepartmentID = D.ID
GO

The content of 'Person' table

Félicitations