traverse data table via stored procedure 1

[rq]

I am required to populate an integer field using a stored procedure. This field resides in an SQL table that contains thousands of records. Normally I would use an UPDATE statement to perform the task of filling in the value for the whole table. What is the efficient solution to this? I'm thinking of creating a stored procedure to copy the table onto a cursor, access the record, call the stored procedure to update the integer field and then fetch the next record.

 

[SQLSister]

No, never use a cursor to update unless the value for the record is dependent on the value of the previous record. Cursors are horribly inefficent and should be avoided as much as possible.

 

[rq]

What is the alternative if cursors are not the way to traverse the whole table for an update?

 

[SQLSister]

Write an update statment that updates all the records in one pass.

Update table1
set field one = field*1.1
where field1 >100

This would update all the records intehtable in one pass which have a value greater than 100 in field one.

If you wanted to update based on a comparison with the values in another table:

Update table1
Set field1 = table2.field1
FROM table1, table2
WHERE table1.id = table2.id
AND table1.price > table2.price

 

[rq]

I'm aware of that approach. The issue is I need to update the field using a stored procedure. This stored procedure genrates a value. Can the SET clause (of the Update command) accept a call to a stored procedure?

 

[SQLSister]

YOu could return the value of the stored procedure to a variable and use the variable in the update. Or are you saying that the value will be differnet for each record?

Inthat case, you should use a user-defined function instead of a stored procedure to generate the value. Those can be used directly in the SQL statement.

 

[rq]

Yes, the value will be different for each record. Thus, the user-defined function is what I'm looking for.

How do you call a user-defined function within an Update statement? Is it something like this --

Update tablename
Set fieldname = functionname()

 

[SQLSister]

yep, of course you would add any input parmeter values in the ()

So you could have something like:

Update table1
Set field1 = dbo.myfunction(table2.field1)
FROM table1, table2
WHERE table1.id = table2.id

You must use the pwner prefix if a table returns a scalar value.