SQL Server execute stored procedure in update statement

Question

every updated record must have different value by using a procedure the procedure returns single integer value

declare @value int;
exec @value = get_proc param;
update table1 set field1 = @value;

this will work for one record but i want the procedure to get new value for each record

Answer 1

Just a quick example of how to use a TVF to perform this type of update:

USE tempdb;
GO

CREATE TABLE dbo.Table1(ID INT, Column1 INT);

INSERT dbo.Table1(ID)
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3;
GO

CREATE FUNCTION dbo.CalculateNewValue
(@ID INT)
RETURNS TABLE
AS
    -- no idea what this logic would be,
    -- just showing an example

    RETURN(SELECT NewValue = @ID + 1);
GO

SELECT t1.ID, n.NewValue
    FROM dbo.Table1 AS t1 
    CROSS APPLY dbo.CalculateNewValue(t1.ID) AS n;

Results:

ID NewValue
-- --------
 1        2
 2        3
 3        4

Now an update that uses the same information:

UPDATE t1 SET Column1 = n.NewValue
    FROM dbo.Table1 AS t1 
    CROSS APPLY dbo.CalculateNewValue(t1.ID) AS n;

SELECT ID, Column1 FROM dbo.Table1;

Results:

ID Column1
-- -------
 1       2
 2       3
 3       4