How to return table from T-SQL Stored Procedure

Question

SQL Newbie here, and I'm having a hell of a time finding 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 in order: 1) Select rows from one table 2) Update rows in another table, using values from the results table in #1 3) Return the results table from #1.

What I can't find is any example about how to return a value like this from a stored procedure. Also, how to retrieve that returned table from the caller (which is another T-SQL script).

Answer 1

See here - a near exact copy of this question.

i found it by searching SO for "How to return table from T-SQL Stored Procedure"

Answer 2

Have a 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