How to use Update statement with output from a Store procedure.

[calvinsinger]

Hi,

Do you know if one do an update of table based on out from a stored proc ?

For example we typically do :

Update some_table set some_col = 'some_value'

But Instead what I want to do is:
Update some_table set some_col=exec some_proc @arg1, @arg2

I have been unable to get this to work. Will appreciate any help

Thank you

 

[swampBoogie]

It seems like you are better of using a function.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp

 

[Hablaras]

You could catch the result in a variable and using output variable(s) in your procedure:

declare @cmd nvarchar(4000)
declare @arg2 blabla

exec some_proc @arg1, @arg2 out

@cmd = 'Update some_table set some_col=' + @arg2

(If @arg2 is not numeric, put it in quotes)

finally:
exec sp_executesql @cmd

 

[calvinsinger]

Hi,

swampboggie, I wanted to use a function, but can not because my stored proc uses temp tables and temp tables, it appears are not allowed in Funcs. Also exec is not allowed as to be used in a function

Hablaras, I am trying to do this without using a cursor.
The arguments for the stored procedure are columns from the table to be update. I basically want to update a particular based on some other columns in the same row of the table.
I will have to loop through all the rows using a cursor, in order to do what you suggest, but cursors are slow and I did not want to use them. Function would be perfect, but functions have all these limitations, it seems

Thanks
Calvin

 

[SQLSister]

YOu could try using the insert statement with the execute function to insert the results into a temp table and then use that in your update.

 

[calvinsinger]

Hi SQLSister,

But I still can not write a function with a exec or with temp tables. I am not sure how what you suggest, will achieve the result.

Thanks
Calvin

 

[SQLSister]

create a temp table (let's call it #temp1, I figure you know how to do this). The structure will match the output of your stored procedure.

insert into #temp1
Exec SP_testprocedure (123, 'hi')

(That should insert the results of your stored procedure into the table)

Update table1
set col2 = #temp1.col2
from table1, #temp1
where table1.Id = #temp1.ID

That will update the col based on the a join to the #temp table.

 

[calvinsinger]

Hi sqlsister,

Thanks for the response. You see the problem is that I do not want to use a cursor. This method, is no different than doing an update, because I still have to loop through the table which I am updating.

The arguments which I want to use for the stored procedure
are contained in the table. That is they are the columns of the table. I basically want to update a particular column based on some other columns in the same row of the table.

A function would be most ideal. But a function does not allow exec or temp tables inside it. So I can not use a function

 

[SQLSister]

my method does not use a cursor, it uses a join to the temp table. Now if your stored procedure only returns one record at atime that could be a problem. In that case I would rewrite it to return all the records you need.