在存储过程中设置结果表名称 [英] set the result table name in stored procedure

问题描述

返回多个结果的存储过程：

a stored procedure that return multiple results:

CREATE PROCEDURE [dbo].[GetMultipleTable]
AS
BEGIN

   if exists (select something from somewhere where somecondition = 1)
   begin
       select * from firsttable
   end
   select * from secondtable
END

当我们执行存储过程时，使用 SqlDataAdapter 的填写方法填写 DataSet 带有多个表。

when we execute the stored procedure, use SqlDataAdapter's Fill method to fill a DataSet with multiple tables.

DataSet ds;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);

我可以得到结果。但我认为我们可以做得比这更好：

I can get the results. but I think we can do better than this:

var index = 0;
if( ds.Tables.Count > 1 ){
   DataTable first = ds.Tables[index];       
   index++;
   // do something
}
DataTable second = ds.Tables[index];
// do something 

我很好奇，如果我们可以通过名称而不是指数。喜欢..

I'm curious if we can get table by name instead of index. like..

DataTable first = ds.Tables["first"];
// do something
if(ds.Tables.Containts("second"))
{
    DataTable second = ds.Tables["second"];
    // do something
}

问题是：是否可以在存储过程中设置结果表的名称？

the question is: is it possible to set the name of the result table in stored procedure?

推荐答案

我猜不是。在某种程度上我怀疑这是一个很好的办法。为什么不在这些情况下返回空的结果集，而不是总是知道第n个结果集的含义。

I guess not. And to some extent I doubt that it is a good way to handle this. Why not just return empty result sets in those cases, than you always know what the nth result set means.

除了将结果呈现为纯文本的情况之外，你需要复杂的逻辑，处理结果的不同情况。

Besides cases where you render the results to plain text, don't you need complicated logic, do deal with the different cases of the result.

你为什么这么做？一个神秘的愿望来提高性能？

Why are you doing it? A mystic wish to improve performance ?

对于临时查询，可能还可以，但存储过程。更好三思

For ad hoc queries it might be OK, but for stored procedures. Better think twice.

也许我的态度受到这个事实的影响，我不得不将这样的过程转换为Oracle，而您没有可变数量的结果集的概念。对于每个结果集，您必须提前提供一个refcursor参数。

Perhaps my attitude is influenced by the fact, that I had to convert such procedures to Oracle and there you do not have the concept of a variable number of result sets. For each result set you have to provide a refcursor parameter in advance.

这篇关于在存储过程中设置结果表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！