Sql Server和.Net如何处理存储过程
我使用存储过程已经超过 1.5 年了。但我从未考虑过如何从 UI 或另一个存储过程中检索数据。
当我编写一个简单的存储过程时。 例如。
CREATE PROCEDURE sp_test
AS
BEGIN
SELECT * FROM tblTest --Considering table has 3 columns.
END
C# 如何将此结果存入 DataTable。
每当我必须在另一个过程中使用此过程的结果时，我认为我们必须使用表数据类型创建一个表值参数，并将其结果分配给表变量。我从来没有尝试过。
CREATE PROCEDURE sp_testcall
AS
BEGIN
@temp = exec sp_test -- I think this would be the way, never tried
END
如果上面的示例代码是正确的，那么使用上面的方法和查询向临时表插入记录有什么区别呢？
CREATE PROCEDURE sp_test
AS
BEGIN
SELECT * INTO #tmp FROM tblTest --Considering table has 3 columns.
END
看来将结果复制到临时表中需要 sql server 的另一项工作。 但幕后会发生什么呢？它会直接将结果的引用分配给表值参数还是使用与临时表相同的过程？
我的问题可能不太清楚。但我会努力改进。
I have been using a stored procedure for more than 1.5 years. But I've never considered how data is retrieved from the UI or within another stored procedure.
When I write a simple stored procedure.
eg.
CREATE PROCEDURE sp_test
AS
BEGIN
SELECT * FROM tblTest --Considering table has 3 columns.
END
How does C# gets this result into DataTable.
Whenever I have to use the result of this procedure in another procedure, I think we have to create a table valued parameter using the table datatype and assign its result to a table variable. I've never tried it.
CREATE PROCEDURE sp_testcall
AS
BEGIN
@temp = exec sp_test -- I think this would be the way, never tried
END
If the above sample code is true, then what is the difference between using the above method and a query to insert records into a temporary table?
CREATE PROCEDURE sp_test
AS
BEGIN
SELECT * INTO #tmp FROM tblTest --Considering table has 3 columns.
END
It would seem that copying the result into a temporary table requires another effort by sql server.
But what would be going on behind the scenes? Would it directly assign references of the result into a table valued parameter or does it use the same process as a temporary table?
My question might not be clear. But I will try to improve.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
对于初级到中级水平，您应该始终将 #temp 表和 @table 变量视为同一枚硬币的两个面。虽然它们之间存在一些差异，但出于所有实际目的，它们的成本相同并且性能几乎相同。唯一的主要区别是 @table 变量不是事务处理的，因此不受回滚的影响。
如果深入了解细节，#temp 表的处理成本稍高（因为它们是事务性的），但另一方面 @table 变量只有变量作用域的生命周期。
至于您的问题提出的其他问题：
Sommarskog 详细分析了过程之间共享数据的主题，请参阅如何在存储过程之间共享数据。
For an beginer to intermediate level you should always consider #temp tables and @table variables two faces of the same coin. While there are some differences between them, for all practical purposes they cost the same and behave nearly identical. The sole major difference is that @table variables are not transacted and hence not affected by rollbacks.
If you drill down into details, #temp tables are slightly more expensive to process (since they are transacted) but on the other hand @table variables have only the lifetime of a variable scope.
As to other issues raised by your question:
INSERT INTO <table> EXEC sp_testThe topic of sharing data between procedures was analyzed at length by Erland Sommarskog, see How to Share Data Between Stored Procedures.
select的意思是“将数据返回给客户端”。 C# 是客户端，因此它获取数据。话又说回来，这并不完全是 C# 做的，而是 ADO.NET 做的。有一个数据提供者知道如何使用网络/内存/其他一些协议与 SQL 服务器通信并读取它生成的数据流。这个特定的客户端（ADO.NET）使用接收到的数据来构造某些类，例如DataTable，其他提供程序可以做完全不同的事情。
所有这些在 SQL Server 级别都是无关紧要的，因为就服务器而言，数据已使用建立连接的协议发送出去，仅此而已。
从内部来看，让存储过程将简单的
选择数据返回给其他任何东西并没有多大意义。当您需要这样做时，您可以显式地告诉 SQL Server 您想要什么，例如将数据插入到两个相关 SP 都可用的临时表中，将数据插入到传递给过程的表值参数中，或者重写您的存储过程作为返回表的函数。
话又说回来，我不太清楚你在问什么。
A
selectmeans "return data to client". C# is a client, therefore it gets the data.Then again, it's not exactly C# that does it, it's ADO.NET. There's a data provider that knows how to use a network/memory/some other protocol to talk to the SQL server and read data streams it generates. This particular client (ADO.NET) uses the received data to construct certain classes, such as DataTable, other providers can do something completely different.
All that is irrelevant at SQL Server level, because as far as the server is concerned, the data has been sent out using the protocol with which the connection was established, that's it.
From inside, it doesn't make much sense to have a stored procedure return simply
selected data to anything else.When you need to do that, you have the means to explicitly tell SQL Server what you want, such as inserting the data into a temporary table available to both involved SPs, inserting data into a table-valued parameter passed to the procedure, or rewriting your stored procedure as a function that returns a table.
Then again, it's not exacly clear to me what you were asking about.