如何从存储过程返回表? [英] How to return a table from a Stored Procedure?

问题描述

这是一个非常简单的问题.

It is very simple question.

我正在尝试从存储过程返回一个表，例如

I am trying to return a table from a stored procedure, like

select * from emp where id=@id

我想将此查询结果作为表格返回.我必须通过存储过程来做到这一点.

I want to return this query result as a table. I have to do this through a stored procedure.

推荐答案

你的问题在哪里??

对于存储过程，只需创建:

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

仅此而已.

从您的 ASP.NET 应用程序中，只需创建一个 SqlConnection 和一个 SqlCommand(不要忘记设置 CommandType = CommandType.StoredProcedure)

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();

然后填写例如带有该数据的 DataTable 并将其绑定到例如一个 GridView.

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

这篇关于如何从存储过程返回表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！