Using stored procedures results in update statement

Question

I've got a table with two columns(among others): id and created_in_variant and a stored procedure that calculates the created_in_variant value basing on the id. I'd like to do something like this:

UPDATE [dbo].[alerts]
     SET [created_in_variant] = find_root [id]

Is there a nice way to do it?

Answer 1

Rewrite the stored procedure as a scalar valued function. Then you can use this for your update.

Answer 2

Can you make the created_in_variant a computed field?

Answer 3

You may want to look at using a scalar valued function (also known as a user defined function) instead of a stored procedure for this type of problem.

EDIT: Here is some information concerning SVFs : Click

EDIT 2: Here is some more information from 15 Seconds

Answer 4

You could use a UDF instead of a stored procedure.

Answer 5

I don't think so. If you turned the stored procedure into scaler function that'd work.

If you wanted to use a stored procedure you have to call the stored procedure for each id (in a cursor) and return the value in a OUTPUT variable then do the update for each id w/ the output variable. However, It'd be much better to use a UDF.

Answer 6

You could reprogram the stored procedure to a user defined function and use that.

Answer 7

Change your proc into a UDF and you basically call it exactly as you got

UPDATE [dbo].[alerts]
     SET [created_in_variant] = dbo.find_root([id])

Answer 8

If you are forced to use stored procedure to implement what you have asked, you can simply save the found root value in a variable and pass it along. There is no simpler way unless you use a UDF (user-defined function) like others have mentioned.

This is one of the simplest answers when using stored procedure because you cannot pass stored procedure directly to the UPDATE statement. There is no interpolation like in other languages like C# or Java.

declare @root int
-- 1) Return the root through output parameter
exec find_root @id, @root out
-- or make the sproc to return the root value
exec @root = find_root @id
UPDATE [dbo].[alerts]
  SET [created_in_variant] = @root

Answer 9

You are simply missing dbo., DJ answer shuold already be good. i had the same exact problem today.