UPDATE with a stored procedure on SQL Server 2005

Question

UPDATE users 
SET field = my_sp()

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.

Answer 1

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.

Answer 2

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

Answer 3

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