SQL 如何从存储过程返回表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5604927/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return a table from a Stored Procedure?
提问by jams
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.
我想将此查询结果作为表返回。我必须通过存储过程来做到这一点。
回答by marc_s
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 SqlConnectionand 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 DataTablewith that data and bind it to e.g. a GridView.
然后DataTable用该数据填充例如 a并将其绑定到例如 GridView。
回答by Anand Thangappan
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
我希望它对你有帮助
回答by makuki
It's VERY important to include:
包括：
SET NOCOUNT ON;
into SP, In First line,
if you do INSERTin SP, the END SELECTcan't return values.
进入SP，在第一行，如果你INSERT在SP 中，END SELECT则不能返回值。
THEN, in vb60 you can:
然后，在 vb60 中，您可以：
SET RS = CN.EXECUTE(SQL)
OR:
或者：
RS.OPEN CN, RS, SQL
