Intro
Cursors can be very useful sometimes. When updating a small part of data, they can easily update the data. There is no need to write another small tool to do this, just write SQL directly
Sample
Here is a practical example:
-- Declare field variables
DECLARE @RegionCode INT;
DECLARE @RegionName NVARCHAR(64);
DECLARE @ProvinceId INT;
-- declare cursor
DECLARE ProvinceCursor CURSOR FOR(
SELECT Id AS ProvinceId, region.RegionCode,region.RegionName FROM dbo.Provinces AS province
JOIN dbo.Regions AS region ON province.Name=SUBSTRING(region.RegionName,1, LEN(province.Name)) AND region.RegionType=1
);
-- Open cursor
OPEN ProvinceCursor;
-- Move cursor, load data
FETCH NEXT FROM ProvinceCursor
INTO @ProvinceId,@RegionCode,@RegionName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Operate according to the cursor data, only the SQL Script, or direct UPDATE，See what you need
PRINT 'UPDATE dbo.Provinces SET Code = ' + CONVERT(NVARCHAR(12), @RegionCode)+', Name = N'''+@RegionName +''' WHERE Id = ' + CONVERT(NVARCHAR(12), @provinceId) +';';
-- Move cursor to next data
FETCH NEXT FROM ProvinceCursor
INTO @ProvinceId,@RegionCode,@RegionName;
END;
CLOSE ProvinceCursor;
DEALLOCATE ProvinceCursor;Another Sample
DECLARE @projectId nvarchar(36) -- Declare variables
DECLARE My_Cursor CURSOR --Define cursors
FOR (SELECT OriginalProjectId FROM dbo.CommunityProjects
WHERE CommunityId = -1) --Find out the required collection and put it in the cursor
OPEN My_Cursor; --Open cursor
FETCH NEXT FROM My_Cursor INTO @projectId;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE dbo.CommunityProjects
SET CommunityId = CAST(ISNULL((
SELECT ZhongyiCommunityId FROM dbo.CommunityMappings
WHERE FangdiCommunityId = @projectId
),'-1') AS INT)
WHERE OriginalProjectId = @projectId
FETCH NEXT FROM My_Cursor INTO @projectId;
END
CLOSE My_Cursor; --Close cursor
DEALLOCATE My_Cursor; --Releasing cursorsand more
DECLARE @RegionCode INT;
DECLARE @RegionName NVARCHAR(64);
DECLARE @provinceId INT;
DECLARE ProvinceCursor CURSOR FOR(
SELECT RegionCode,
RegionName
FROM dbo.Regions
WHERE RegionType = 1);
OPEN ProvinceCursor;
FETCH NEXT FROM ProvinceCursor
INTO @RegionCode,
@RegionName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @provinceId =ISNULL((SELECT Id FROM dbo.Provinces WHERE Name = @RegionName), 0);
IF @provinceId > 0
PRINT 'UPDATE dbo.Provinces SET Code = ' + CONVERT(NVARCHAR(12), @RegionCode)+' WHERE Id = ' + CONVERT(NVARCHAR(12), @provinceId) +';';
ELSE
PRINT 'INSERT INTO dbo.Provinces(Name,Code) VALUES(N''' + @RegionName + ''',' + CONVERT(NVARCHAR(12), @RegionCode)+ ');';
FETCH NEXT FROM ProvinceCursor
INTO @RegionCode,
@RegionName;
END;
CLOSE ProvinceCursor;
DEALLOCATE ProvinceCursor;More
When doing some small data operations, cursors are very convenient, and cursors are flexible. You can only generate SQL to update the data, or print out the values before and after the data update, so as to recover the data after the error data update