4

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).

Whiteknight
  • 475
  • 2
  • 5
  • 13
  • [See here](http://stackoverflow.com/questions/440308/tsql-returning-a-table-from-a-function-or-store-procedure) - a near exact copy of this question. i found it by searching SO for "How to return table from T-SQL Stored Procedure" – Paul Sasik Sep 30 '09 at 13:18

1 Answers1

5

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
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • Okay, so what's the syntax inside YourSP that allows me to return a value? – Whiteknight Sep 30 '09 at 13:27
  • 3
    the rows matching the last select will be returned – CSharpAtl Sep 30 '09 at 13:42
  • That's exactly the information I was looking for. Thanks CSharpAtl – Whiteknight Sep 30 '09 at 13:43
  • +1, But one small correction: You can't use a table variable to receive the output from an INSERT EXEC statement. It has to be a standard (normal static table) or temp table (#table). – Stu Pegg Sep 30 '09 at 18:54
  • We have done so in sql server 2005, have you tried it? It does not allow insert into ### select , but execs are fine. – Adriaan Stander Sep 30 '09 at 19:21
  • I've tried it in SQL server 2005 previously, perhaps there's a version or setting difference between our setups? I'll double-check tomorrow anyway. – Stu Pegg Sep 30 '09 at 19:40
  • 1
    I double checked, and a insert into @table exec sp. I doublke checked the documentation for INSERT INTO from sql server 2005. it states "A table variable, within its scope, can be used as a table source in an INSERT statement." – Adriaan Stander Oct 01 '09 at 05:08