replacing a cursor

[habneh]

I always think replacing all the cursors defined in all my procedures.

I was able to do it for those that have column (primary key or unique key) to identify each row uniquely with the while loop

but I can't do it for those who don't have a uniue key.

how can I replace those cursors defined on these kind of queries

take for example on this record set
oty_id stu_id ppt_id RDate R2Date
326 71 28557 NULL NULL
326 71 28558 NULL NULL
326 71 28559 NULL NULL
326 71 28560 NULL NULL
326 194 409 NULL NULL
326 194 410 NULL NULL
326 194 411 NULL NULL
326 207 1133 NULL NULL
326 207 1134 NULL NULL
326 207 1135 NULL NULL

how can I loop through this recordset with out defining any cursor

Thanks,

 

[RiverGuy]

Looping through a recordset isn't really a job for T-SQL. A table without a unique key is asking for trouble IMO. I would at least add an identity field.

What are you trying to do in this particular case?

 

[habneh]

This result set is not coming from a single table, there are like five tables joined to get this recordset. few of them from one table others from another table.

here is what I am planning to do, for each RDate column, if it is not null, I am going to update col1 of one of the five tables.


let say if it were cursor

---cursor declared
fetch cursor
while loop
if @RDate is not null
update tbl1
set ...
fetch cursor
end
close cursor
deallocate cursor

how can I do all this process by without cursor

Thanks

 

[philhege]

Any sort of looping construct can be represented using a temp table or table variable.

SELECT *, 0 as processed
INTO #worktable
FROM (Your table, view, whatever)

WHILE (select count(*) from #worktable where processed = 0)
>0

BEGIN
select top 1 @variable = column, ...--other variables
where processed = 0
--do stuff with the values
--
--
UPDATE #worktable set processed = 1 where --(your key fields equal the variable values)
END

I see a unique identifier in each of your sample rows. It's call a concatenated key, and is the combination of the first three fields. Set variables equal to each of these, and set your processing flag based on these fields.