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.
Can an SQL procedure return a table?
A PL/SQL function can return a nested table. Provided we declare the nested table as a SQL type we can use it as the source of a query, using the the TABLE() function.
Here is a type, and a nested table built from it:
SQL> create or replace type emp_dets as object (
2 empno number,
3 ename varchar2(30),
4 job varchar2(20));
5 /
Type created.
SQL> create or replace type emp_dets_nt as table of emp_dets;
2 /
Type created.
SQL>
Here is a function which returns that nested table ...
create or replace function get_emp_dets (p_dno in emp.deptno%type)
return emp_dets_nt
is
return_value emp_dets_nt;
begin
select emp_dets(empno, ename, job)
bulk collect into return_value
from emp
where deptno = p_dno;
return return_value;
end;
/
... and this is how it works:
SQL> select *
2 from table(get_emp_dets(10))
3 /
EMPNO ENAME JOB
---------- ------------------------------ --------------------
7782 CLARK MANAGER
7839 KING PRESIDENT
7934 MILLER CLERK
SQL>
SQL Types offer us a great deal of functionality, and allow us to build quite sophisticated APIs in PL/SQL. Find out more.
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
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.
Related Topics
Deleting a Lot of Data in Oracle
How to Get a Real Time Within Postgresql Transaction
Way to Abort Execution of MySQL Scripts (Raising Error Perhaps)
How to Avoid SQL Query Timeout
Postgres - Comparing Two Arrays
How to Select MySQL Rows in The Order of in Clause
Grant Permission to Create Tables - SQL Server
Creating a Table from a Query Using a Different Tablespace (Oracle Sql)
Sql-Style Group by Aggregate Functions in Jq (Count, Sum and etc)
Can You Have an Inner Join Without the on Keyword
How to Display the Date as Mm/Dd/Yyyy Hh:Mm Am/Pm Using SQL Server 2008 R2
Rails Activerecord Query Using Inner Join
Using a View with No Primary Key with Entity
How to Exclude Tables from Sp_Msforeachtable