I have a stored procedure SP1 that receives a parameter @param1 and does a bunch of things with it and returns a value.
Now I have a table consisting of two columns, say C1 and C2. Initially, C1 has different values on each row while C2 is 0 on every row. Now I want to update C2 with the value returned by SP1 with its corresponding C1 value as @param1. I was hoping something like:
update Table1 set C2 = (exec SP1 @param1=C1)
OUTPUT parameter inside your stored procedure and use that Parameter to store the value and then use that parameter inside your Update statement. Something like this....
DECLARE @OutParam Datatype; EXECUTE SP1 @param1=C1, @OUT_Param = @OutParam OUTPUT --<-- --Now you can use this OUTPUT parameter in your Update statement. UPDATE Table1 SET C2 = @OutParam
After reading your comments I think this is what you are trying to do pass value of C1 Column from Table Table1 to Stored Procedure and then Update the Relevant C2 Column of Table1 with the returned value of stored procedure.
For this best way to do is to Create a Table Type Parameter and pass the values of C1 as a table.
See here for a detailed answer about how to pass a table to a stored procedure.
I havent tested it But in this situation I guess you could do something like this.. I dont recomend this method if you have a large table. in that case you are better off with a table type parameter Procedure.
-- Get C1 Values In a Temp Table SELECT DISTINCT C1 INTO #temp FROM Table1 -- Declare Two Varibles --1) Return Type of Stored Procedure --2) Datatype of C1 DECLARE @C1_Var DataType; DECLARE @param1 DataType; WHILE EXISTS(SELECT * FROM #temp) BEGIN -- Select Top 1 C1 to @C1_Var SELECT TOP 1 @C1_Var = C1 FROM #temp --Execute Proc and returned Value in @param1 EXECUTE SP1 @param1 = @C1_Var -- Update the table UPDATE Table1 SET C2 = @param1 WHERE C1 = @C1_Var -- Delete from Temp Table to entually exit the loop DELETE FROM #temp WHERE C1 = @Var END