在存储过程中设置结果表名 [英] 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的Fill 方法用多个表填充 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屋！