Cursor doesn't loop properly???

9 Jun 2006 2:42 PM

Tim::..

Can someone please tell me why this doesn't work???

I am trying to loop through a table and populate a column called
LocalIDNumber with random numbers... But there must not be any duplicates!

This runs perfectly but only populates the first record and not the other
5000 records in the database...

PLEASE, PLEASE Help....

Thanks

....SQL...

DECLARE RandomCursor CURSOR FOR
        SELECT personNo FROM person

OPEN RandomCursor
        --Get a variable for the id of the record we are going 2 update
        DECLARE @IDField as int

        -- Perform the first fetch.
        FETCH NEXT FROM RandomCursor

        -- Get the data from the cursor into local variables
        INTO @IDField

        -- Check @@FETCH_STATUS to see if there are any more ros to fetch.
        WHILE @@FETCH_STATUS = 0
    BEGIN
        -- This is executed as long as the previous fetch succeeds.

        FETCH NEXT FROM RandomCursor

        DECLARE @Random int;
        DECLARE @Upper int;
        DECLARE @Lower int

        -- This will create a random number between 10000 and 99999
        SET @Lower = 10000 -- The lowest random number
        SET @Upper = 99999 -- The highest random number
        SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)

        UPDATE
            person

        SET LocalIDNumber = @Random

        WHERE personNo= @IDField
END

CLOSE RandomCursor

DEALLOCATE RandomCursor

9 Jun 2006 2:59 PM

SQL Menace

shouldn't this
FETCH NEXT FROM RandomCursor
--Something is missing here!!!!!!!!!!!!!!!!!!!

                DECLARE @Random int;
                DECLARE @Upper int;
                DECLARE @Lower int

be

FETCH NEXT FROM RandomCursor
INTO @IDField   -- Here we go ;-)

                DECLARE @Random int;
                DECLARE @Upper int;
                DECLARE @Lower int

Denis the SQL Menace
http://sqlservercode.blogspot.com/

... wrote:
Show quote

> Can someone please tell me why this doesn't work???
>
> I am trying to loop through a table and populate a column called
> LocalIDNumber with random numbers... But there must not be any duplicates!
>
> This runs perfectly but only populates the first record and not the other
> 5000 records in the database...
>
> PLEASE, PLEASE Help....
>
> Thanks
>
> ...SQL...
>
> DECLARE RandomCursor CURSOR FOR
>         SELECT personNo FROM person
>
>
> OPEN RandomCursor
>         --Get a variable for the id of the record we are going 2 update
>         DECLARE @IDField as int
>
>         -- Perform the first fetch.
>         FETCH NEXT FROM RandomCursor
>
>
>         -- Get the data from the cursor into local variables
>         INTO @IDField
>
>         -- Check @@FETCH_STATUS to see if there are any more ros to fetch.
>         WHILE @@FETCH_STATUS = 0
>     BEGIN
>         -- This is executed as long as the previous fetch succeeds.
>
>         FETCH NEXT FROM RandomCursor
>
>         DECLARE @Random int;
>         DECLARE @Upper int;
>         DECLARE @Lower int
>
>         -- This will create a random number between 10000 and 99999
>         SET @Lower = 10000 -- The lowest random number
>         SET @Upper = 99999 -- The highest random number
>         SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)
>
>         UPDATE
>             person
>
>         SET LocalIDNumber = @Random
>
>         WHERE personNo= @IDField
> END
>
> CLOSE RandomCursor
>
> DEALLOCATE RandomCursor

9 Jun 2006 3:02 PM

David Browne

Show quote

"Tim::.." <myatix_at_hotmail.com> wrote in message
news:31C2DFA2-38E6-4217-B597-5C0F9F1CD326@microsoft.com...
> Can someone please tell me why this doesn't work???
>
> I am trying to loop through a table and populate a column called
> LocalIDNumber with random numbers... But there must not be any duplicates!
>
> This runs perfectly but only populates the first record and not the other
> 5000 records in the database...
>
> PLEASE, PLEASE Help....
>
> Thanks
>
> ...SQL...
> DECLARE RandomCursor CURSOR FOR

SELECT personNo FROM person

OPEN RandomCursor
--Get a variable for the id of the record we are going 2 update
DECLARE @IDField as int

-- Perform the first fetch.
-- Get the data from the cursor into local variables
FETCH NEXT FROM RandomCursor INTO @IDField

-- Check @@FETCH_STATUS to see if there are any more ros to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.

DECLARE @Random int;
DECLARE @Upper int;
DECLARE @Lower int

-- This will create a random number between 10000 and 99999
SET @Lower = 10000 -- The lowest random number
SET @Upper = 99999 -- The highest random number
SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)

UPDATE
person

SET LocalIDNumber = @Random

WHERE personNo= @IDField
FETCH NEXT FROM RandomCursor INTO @IDField
END

David

9 Jun 2006 3:04 PM

Alexander Kuznetsov

you missed INTO @IDField clause in your second fetch. Note that you can
get duplicates. To get rid of them, use this:

select
1 id, 1 rand_num
into #t
union all
select 2, 0
union all
select 3, 5
union all
--- rand() generated a duplicate
select 4, 1
union all
select 5, 17
union all
select 6, 3
union all
select 7, 13
union all
select 8, 9
union all
select 9, 4
union all
select 10, 21
go
select * from #t
--- remove duplicates
update #t set rand_num = rand_num + (select count(*) from #t t1
where t1.rand_num < #t.rand_num or (t1.rand_num = #t.rand_num and
t1.id < #t.id))
go
select * from #t