Archive for the ‘Cursors’ Category

The Problem

I had a table which represented clusters of an incident. I then wanted 1 row for every instance of that incidence. So,

DayName InstanceCount
Day 1 2
Day2 3
Day3 1

Would become:

DayName
Day1
Day1
Day2
Day2
Day2
Day3

The Solution

At first I used a cursor and entered each row into a new table.
I wasn’t happy with the cursor so looked into other answers and came up with this:

DECLARE @ii as int;
DECLARE @i as int = 0;
SELECT @ii = max(InstanceCount) + 10
FROM [dbo].[SourceTable];

DECLARE @NumRows as Table (num int not null);
WHILE @i < @ii
BEGIN
INSERT INTO @NumRows(num)
VALUES (@i);
SET @i = @i + 1;
END;

SELECT J.DayName
FROM [dbo].[SourceTable] as J
JOIN @NumRows as n
ON n.num < CONVERT(int, LEFT(CAST(InstanceCount AS INT ),20));

First get the highest instance count and add a few for luck. Then join this numbers table to your base table. This got the results I wanted. (I still had to use a cursor because I needed to randomise an element in each row, but that’s another story).

I watched a video by Steve Stedman (http://stevestedman.com/) and he gave a demo of a numbers table using a CTE. So I changed my code to this:

DECLARE @ii as int;
SELECT @ii = max(InstanceCount) + 10
FROM [dbo].[SourceTable];

;WITH numbers(num) as
(
SELECT 1
UNION ALL
SELECT 1 + num
FROM numbers
WHERE num < @ii
)
SELECT J.DayName
FROM [dbo].[SourceTable] as J
JOIN numbers as n
ON n.num < CONVERT(int, LEFT(CAST(InstanceCount AS INT ),20));
OPTION (MAXRECURSION 0);

Annoyingly, MAXRECURSION wouldn’t accept a variable as a value else I would have used @ii

Advertisements

SQL Cursors

Posted: April 19, 2010 in Cursors, TSQL

I had thought that if an item came within teh scope of a cursor it would remain there until removed. However, cursors work a little like views, if you cange a value which would remove an item from a view you will no longer see it in the view. If you change the value of a data row which would put it outside the scope of teh cursor the cursor will drop that row.

This was tested on SQL 2005 sp3 – I assume it will be the same on other versions but I haven’t tested them.

I have a simple table:

CREATE TABLE [dbo].[test](

[pkid] [int] IDENTITY(1,1) NOT NULL,

[descript] [varchar](5) NOT NULL,

[value1] [int] NOT NULL,

[value2] [int] NOT NULL,

CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED

(

[pkid] ASC

);

The table has 3 rows:

Pkid descript value1 value2
1 test1 0 0
2 test2 0 0
3 test1 0 0

I then execute this script against the table:

Declare @dbName as varchar(5);

Declare @x as int;

Set @x = 1;

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select descript From dbo.Test

WHERE value2 = 0;

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

Print @dbName

Update dbo.Test

Set value2 = @x

Where descript = @dbname;

Set @x = @x+1;

FETCH NEXT FROM dbCursor INTO @dbName;

END

CLOSE dbCursor;

DEALLOCATE dbCursor;

The results in the table are:

Pkid descript value1 value2
1 test1 0 1
2 test2 0 2
3 test1 0 1

This shows that because the value2 value of pkid 3 changed from 0 to 1 on the first update it dropped out of the cursors scope.