Xufeng Xufeng - 12 days ago 5
SQL Question

How to set a value with the return value of a stored procedure

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)


just like receiving a return value from a function in most programming languages.

I haven't learned any SQL, but I did a lot of research not finding anything. So any help would be appreciated.

Answer

Create an 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

UPDATE

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