How to return a table from a T-SQL stored procedure

Question

SQL Newbie is here, and I have time to find what should be a simple code example to answer what I think is a simple question.

I need to write a stored procedure that does three things: 1) Select the rows from one table 2) Update the rows in another table using the values ​​from the result table in # 1 3) Return the result table from # 1.

What I cannot find is an example of how to return such a value from a stored procedure. Also, how to get this returned table from the caller (which is another T-SQL script).

Answer 1

Look at this.

DECLARE @Table1 TABLE( ID INT, VAL int ) INSERT INTO @Table1 (ID,VAL) SELECT 1, 1 INSERT INTO @Table1 (ID,VAL) SELECT 2, 2 INSERT INTO @Table1 (ID,VAL) SELECT 3, 3 DECLARE @Table2 TABLE( ID INT, VAL VARCHAR(MAX) ) INSERT INTO @Table2 (ID,VAL) SELECT 1, 1 INSERT INTO @Table2 (ID,VAL) SELECT 2, 2 INSERT INTO @Table2 (ID,VAL) SELECT 3, 3 --Lets say this is the 2 tables --now this will go into the sp UPDATE @Table1 SET Val = t1.Val + t2.Val FROM @Table1 t1 INNER JOIN @Table2 t2 ON t1.ID = t2.ID SELECT t1.* FROM @Table1 t1 INNER JOIN @Table2 t2 ON t1.ID = t2.ID --and you can insert into a var table in the tsql script that calls the sp DECLARE @Table1TSQL TABLE( ID INT, VAL int ) INSERT INTO @Table1TSQL (ID,VAL) EXEC YourSP