Hello;
We are new to SQL Server. Using 2008 R2. Creating a stored proc.. It
contains a curser that is a select statement--selects one distinct
field from a table, sorted by that field.

In our loop--we want to --based on the value of the cursor field --
update another field in the table.

like this:

if cursor field is CURFLD and field we want to update in the table is
NUMFLD then:

first loop: value of CURFLD is 10 -- we want NUMFLD to get updated
with a value of 1.

next loop: value of CURFLD is 13 -- we want NUMFLD to get updated
with a value of 2.

and so on until the loop finishes.

Here is what we have so far--it is not working--also probably a syntax
error.

CREATE PROC v_pids --SendEmailCursor

AS

-- Cursor Variables --
DECLARE @Id smallint;

------------------ CURSOR eMail --------------------
DECLARE CREATE_PIDS CURSOR FAST_FORWARD FOR
SELECT distinct MAPBLOLOT
FROM p_real
order by MAPBLOLOT

OPEN CREATE_PIDS

FETCH NEXT FROM CREATE_PIDS INTO @Id

WHILE @@FETCH_STATUS = 0
BEGIN
---
SET @Id = ROW_NUMBER() OVER (ORDER BY MAPBLOLOT)

UPDATE p_real SET v_pid = v_pid + 1
wHERE id = @Id
---
FETCH NEXT FROM CREATE_PIDS INTO @Id
END

CLOSE CREATE_PIDS
DEALLOCATE CREATE_PIDS
------------------ CURSOR eMail (END) --------------------

GO

thanks in advance for any assistance.

Sincerely,

Tony

If I understand what your post correctly, this solution should work for you:

WITH CTE AS (
SELECT v_pid, dense_rank() OVER (ORDER BY MAPBLOLOT) AS newvalue
FROM p_real
)
UPDATE CTE
SET v_pid = newvalue

There is no need for a cursor here.

In the query above, I first define a CTE (Common Table Expression) where I
return the column to update, and the numbering you want. You may want to run
that query first (with the MAPBLOLOT column added) to see that the results
are what you expected.

Then I update the CTE. This looks like little funny, but SQL Server finds
the way back to the base table and performs the update.

The roundabout with the CTE is needed, because of the dense_rank() function;
it would not be possible to use it directly in the SET clause.