Geek asks and answers

How to set value with return value of stored procedure

I have a stored procedure SP1 that takes the @ param1 parameter and does a bunch of things with it and returns a value.

Now I have a table consisting of two columns, for example C1 and C2. Initially, C1 has different values ​​for each row, and C2 has 0 in each row. Now I want to update C2 with the value returned by SP1, with its corresponding C1 value as @ param1. I was hoping for something like:

update Table1 set C2 = (exec SP1 @param1=C1)

same as getting the return value from a function in most programming languages.

I did not learn SQL, but I learned a lot without finding anything. Therefore any help would be appreciated.

+3
source share
2 answers

OUTPUT , Update, - ....

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

, , , , C1 Table1 , C2 1 .

- C1 . See here , .

, , , - . , . .

-- 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
+1

, .

, UDF:

CREATE FUNCTION FN1 (@funcParam1 <param type>)
RETURNS <return type>
AS
BEGIN
    DECLARE @return <return type>;

    -- Do whatever you have to do here

    RETURN @return;
END

:

UPDATE Table1 SET C2 = FN1(C1)

, , , . .

-2

More articles:


All Articles