如何从存储过程返回表?

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.

我想返回这个查询结果作为一个表。我必须通过存储过程来实现这一点。

3 个解决方案

#1

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)

从你的ASP。NET应用程序，只需创建一个SqlConnection和一个SqlCommand(不要忘记设置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.

然后将数据填入例如DataTable，并将其绑定到GridView中。

#2

In SQL Server 2008 you can use

在SQL Server 2008中，您可以使用

http://www.sommarskog.se/share_data.html#tableparam

http://www.sommarskog.se/share_data.html tableparam

or else simple and same as common execution

或者简单的和普通的执行一样

CREATE PROCEDURE OrderSummary @MaxQuantity INT OUTPUT AS

SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity)
FROM Orders AS Ord
     JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID)
GROUP BY Ord.EmployeeID
ORDER BY Ord.EmployeeID

SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]

RETURN (SELECT SUM(Quantity) FROM [Order Details])
GO

I hopes its help to you

我希望这对你有帮助

#3

VERY important include

非常重要的包括

 SET NOCOUNT ON;

into SP, In First line, if you do INSERT in SP, the END SELECT can't return values.

进入SP，在第一行，如果你插入SP，结束选择不能返回值。

THEN, in vb60 you can 
SET RS = CN.EXECUTE(SQL)

或

RS.OPEN CN,RS, SQL

#1