How to Return a Table from a Stored Procedure

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

How to return a table from a Stored Procedure?

Where is your problem??

For the stored procedure, just create:

CREATE PROCEDURE dbo.ReadEmployees @EmpID INT
AS
   SELECT *  -- I would *strongly* recommend specifying the columns EXPLICITLY
   FROM dbo.Emp
   WHERE ID = @EmpID

That's all there is.

From your ASP.NET application, just create a SqlConnection and a SqlCommand (don't forget to set the CommandType = CommandType.StoredProcedure)

DataTable tblEmployees = new DataTable();

using(SqlConnection _con = new SqlConnection("your-connection-string-here"))
using(SqlCommand _cmd = new SqlCommand("ReadEmployees", _con))
{
    _cmd.CommandType = CommandType.StoredProcedure;

    _cmd.Parameters.Add(new SqlParameter("@EmpID", SqlDbType.Int));
    _cmd.Parameters["@EmpID"].Value = 42;

    SqlDataAdapter _dap = new SqlDataAdapter(_cmd);

    _dap.Fill(tblEmployees);
}

YourGridView.DataSource = tblEmployees;
YourGridView.DataBind();

and then fill e.g. a DataTable with that data and bind it to e.g. a GridView.

Return table in stored procedure postgresql

Stored procedures aren't meant to return anything, use a function. And you don't need PL/pgSQL for that either:

create or replace FUNCTION public.test()
  returns TABLE (id   numeric, test varchar) 
AS
$func$
  SELECT * 
  FROM public.test;
$func$ 
LANGUAGE sql;

As you return all columns of one table, you can also use returns setof

create or replace FUNCTION public.test()
  returns setof public.test
AS
$func$
  SELECT * 
  FROM public.test;
$func$ 
LANGUAGE sql;

Then use it like a table:

select *
from test();

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.

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;

How to return the count of a table created and dropped within a SQL stored procedure in Snowflake?

Using INTO:

LET UPDATED_ROW_COUNT INT;

select count(*) 
INTO :UPDATED_ROW_COUNT 
FROM DOMAIN_CANONICAL.MANUFACTURER_ITEM_temp MI 
INNER JOIN DOMAIN_CANONICAL.TEMP_DELTA_MANUFACTURER_ITEM DMI
  ON DMI.GHX_INTERNAL_ITEM_ID = MI.GHX_INTERNAL_ITEM_ID
where MI.HASH_DELTA != DMI.HASH_DELTA;

Related: Setting Variables to the Results of a SELECT Statement

Can I get an Example of a Snowflake Stored Procedure that takes in StartDate and ReturnDate then executes 2 Select statements and returns a table()?

If you want to return a table, you must use a SQL Script stored procedure. I'm not sure what you want the second select to do, so here's a sample with a single select. Are you looking for the second one to key off of something it finds after running the first statement?

create or replace procedure test (start_date date, end_date date)
returns table()
language sql
as
$$

declare
  res resultset default (
    select  *
    from    "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."ORDERS" 
    where   O_ORDERDATE >= :start_date
        and O_ORDERDATE <= :end_date
   );
begin
  return table(res);
end;

$$;

call test('1994-01-01', '1994-01-02');