UPDATE with a stored procedure on SQL Server 2005

UPDATE users 
SET field = my_s开发者_如何学Gop()

in SQL Server 2005. Apparently I can't do this and have to use some form of EXEC. Can anyone help me out and let me know how to do this? This should be some easy rep.

To assign value you need to use sql function. it is impossible to assign value from stored procedure.
Here is link how to create it.

you need to write a scalar function that takes some parameters (or even zero) and returns what you need.

You could store the output of the stored procedure in a temp table, then use that temp table as the basis for your update. As an example, the code below assumes your proc returns a record set with two integers.

create table #t (
    ColumnA int,
    ColumnB int
)

insert into #t
    (ColumnA, ColumnB)
    exec my_sp

update u
    set field = t.ColumnB
    from users u
        inner join #t t
            on u.UserID = t.ColumnA

drop table #t