Can a Stored Procedure/Function Return a Table

SQL server stored procedure return a table

A procedure can't return a table as such. However you can select from a table in a procedure and direct it into a table (or table variable) like this:

create procedure p_x
as
begin
declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t values('a', 1,1,1)
insert @t values('b', 2,2,2)

select * from @t
end
go

declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t
exec p_x

select * from @t

Can a stored procedure/function return a table?

As for now, this is not possible.

Here is the documentation on what may be used in the FROM clause:

table_references:
    table_reference [, table_reference] ...

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [[AS] alias] [index_hint)]
  | table_subquery [AS] alias
  | ( table_references )
  | { OJ table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON conditional_expr
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

index_hint:
    USE {INDEX|KEY} [FOR JOIN] (index_list)
  | IGNORE {INDEX|KEY} [FOR JOIN] (index_list)
  | FORCE {INDEX|KEY} [FOR JOIN] (index_list)

index_list:
    index_name [, index_name] ...

As you can see, stored procedures are not in this list.

Return table from stored procedure / function after doing merge function SQL Server

A stored procedure can certainly return a result set, which the client can consume directly just like a regular SELECT statement.

That said, there are a range of options, none of which are perfect. Each suits a different scenario, and are described at length by Erland Sommarskog in How to Share Data between Stored Procedures:

• Table-valued Functions
• Inline Functions
• Multi-statement Functions
• Using a Table
• Sharing a Temp Table
• Process-keyed Table
• INSERT-EXEC
• Using SQLCLR
• OPENQUERY
• XML
• Cursor Variables

For example, you may not wish to use a permanent table, but a temporary table created by the client and populated by the stored procedure can work well, if directly consuming the results of a SELECT inside the procedure is not suitable.

How to return a user defined table type from a stored procedure

You just CANNOT return table-valued variable from stored procedure.

As you said, you cannot use table-valued parameter if it's not declared as readonly.

In your simple case you can use an inline table-valued function instead:

create function  getCarDetails( @carNumber varchar(20))
returns table
as
  return 
       select CarNumber, Model
       from dbo.cars
       where CarNumber = @carNumber;

Can I call a stored procedure in jdbc which returns a table using mysql?

Yes. It's possible to call a MySQL stored procedure to return a resultset using JDBC.

Examples provided here:

https://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html#calling_javadb_mysql

Pass a table and return a table to a stored procedure or function?

Since your code just does calculations on data (i.e. no side-effects or updates of tables) and isn't coupled to tables, what you can do is to can create a table valued function to do these calculations - the function can take in the table of inputs (R_MODS)as a table type and also return a table of outputs (Sen etc).

Here's a SqlFiddle example.

In detail:

You'll need to create a table type for the inputs, e.g.

CREATE TYPE R_MODS_TYPE AS TABLE(
  SUBJECTID varchar(max),
  ResultCall varchar(max)
);

And define the function as such:

CREATE FUNCTION dbo.DoCalcs(@TheRMods R_MODS_TYPE READONLY)
RETURNS @Result TABLE
(
    [SEN] DECIMAL(10,4), 
    [SenL] DECIMAL(10,4), 
    [SenU] DECIMAL(10,4),  
    [1-SPE] DECIMAL(10,4), 
    [1-SPEL] DECIMAL(10,4), 
    [1-SpeU] DECIMAL(10,4)
)
AS
BEGIN
    DECLARE @TP float, @TN float, @FP float, @FN float, @SEN float, @SPE float, 
            @M1 float, @M2 float, @Sen95 float, @SpeL float , @SpeU float, 
            @SenU float, @SenL float;

    SET @TP = (SELECT COUNT(SUBJECTID) FROM @TheRMods WHERE ResultCall='TP')
    SET @TN = (SELECT COUNT(SUBJECTID) FROM @TheRMods WHERE ResultCall='TN')
    SET @FP = (SELECT COUNT(SUBJECTID) FROM @TheRMods WHERE ResultCall='FP')
    SET @FN = (SELECT COUNT(SUBJECTID) FROM @TheRMods WHERE ResultCall='FN')

    SET @SEN = @TP/(@TP + @FN)
    SET @M1 = @TP + @FN 

    SET @SPE = @TN/(@TN + @FP)
    SET @M2 =  @FP + @TN 

    SET @SenL = ( 2*@M1*@SEN + POWER(1.96,2) - 1 - 1.96 * SQRT(POWER(1.96,2) 
          - 2 -(1/@M1)+ 4*@SEN *(@M1*(1-@SEN) + 1)))/(2*(@M1+POWER(1.96,2)))
    SET @SenU = ( 2*@M1*@SEN + POWER(1.96,2) + 1 + 1.96 * SQRT(POWER(1.96,2) 
          + 2 -(1/@M1)+ 4*@SEN *(@M1*(1-@SEN) - 1)))/(2*(@M1+POWER(1.96,2)))

    SET @SpeL = ( 2*@M2*@SPE + POWER(1.96,2) - 1 - 1.96 * SQRT(POWER(1.96,2) 
      - 2 -(1/@M2)+ 4*@SPE *(@M2*(1-@SPE) + 1)))/(2*(@M2+POWER(1.96,2)))
    SET @SpeU = ( 2*@M2*@SPE + POWER(1.96,2) + 1 + 1.96 * SQRT(POWER(1.96,2) 
      + 2 -(1/@M2)+ 4*@SPE *(@M2*(1-@SPE) - 1)))/(2*(@M2+POWER(1.96,2)))

    INSERT INTO @Result ([SEN], [SenL], [SenU], [1-SPE], [1-SPEL], [1-SpeU])
        SELECT @SEN, @SenL, @SenU, 1-@SPE, 1-@SPEL, 1-@SpeU;
    RETURN;
END

You then invoke the table function by declaring an instance of the table type, populating it, and passing it to your function:

DECLARE @TestData R_MODS_TYPE;

INSERT INTO @TestData VALUES ('11-0001','TP'),
('11-0002','TP'),
('11-0003','TP'),
('11-0004','TP'),
... etc.

SELECT * FROM dbo.DoCalcs(@TestData);

Result:

SEN       SenL     SenU    1-SPE   1-SPEL  1-SpeU
--------- -------- ------- ------- ------- -------
1.0000    0.7166   0.9929  0.5000  0.9081  0.0919

Returning a table in firebird 3.0 with stored function or stored procedure

What you are looking for is a selectable stored procedure. Firebird requires you to explicitly declare the columns the stored procedure returns, so something like returns table is not an option. For example:

create procedure sp_test_procedure (x integer) 
  returns (column1 integer, column2 varchar(50))
as
begin
  for select value1, value2 
      from table_name SD
      where exists (
        SELECT DISTINCT S.PARENT_ID 
        FROM table_name AS S 
        WHERE S.COMPONENT_ID = 10011 
        AND S.CARRIER_GROUP_ID = :X 
        AND SD.SD_ID = S.PARENT_ID)
      into column1, column2
  do
  begin
    suspend;
  end
end

You will need to explicitly map the columns, so a simple select * is not a good idea.

Note the use of for select, which selects zero or more rows and iterates over the cursor, and suspend, which outputs a row to be fetched from the stored procedure (in this case for each row of the cursor).

You can produce values from this procedure like:

select column1, column2
from sp_test_procedure(10)

---

Related Topics