从另一个存储过程返回结果集

Technical Environment: ASP.NET and SQL Server

技术环境:ASP.NET和SQL Server

I have a requirement to create a stored procedure which will call another stored procedure and return the output of other stored procedure.

我需要创建一个存储过程,它将调用另一个存储过程并返回其他存储过程的输出。

For example storedprocedure1 should call storedprocedure2 and return that output.

例如,storedprocedure1应该调用storedprocedure2并返回该输出。

Issue: how can get the result set from SQL Server using OUTPUT variable and loop through through the result set in asp.net? I know what to do in the asp.net but I am looking from SQL Server.

问题:如何使用OUTPUT变量从SQL Server获取结果集并循环遍历asp.net中的结果集?我知道在asp.net中该做什么,但我从SQL Server看。

What I have written:

我写的:

alter procedure storedprocedure1
   @Hearing  Varchar(50) output
AS
   SET NOCOUNT ON;

   declare @xid  varchar(50);

   DECLARE db_cursor CURSOR FOR  
      SELECT  WANTEDCOLUMN
      FROM [X] 

   OPEN db_cursor 

   FETCH NEXT FROM db_cursor INTO @xid  //loop through each xid

   WHILE @@FETCH_STATUS = 0   
   BEGIN      
       EXEC @HearingCursor = storedprocedure2 @xid;    
   END   

   CLOSE db_cursor 
   DEALLOCATE db_cursor;

What I want:

我想要的是:

I need to loop through the result set and perform operations based on each value in asp.net. I cannot use cursor as output parameter in SQL Server as there is no matching parameter in asp.net.

我需要遍历结果集并根据asp.net中的每个值执行操作。我不能在SQL Server中使用cursor作为输出参数,因为asp.net中没有匹配的参数。

1 个解决方案

#1

Unless there are some requirements you are not including in your question, there's no reason to create another stored procedure just so you can use an output variable. Just fill a dataset with the results of the original stored procedure. Something like:

除非您的问题中没有包含某些要求,否则没有理由创建另一个存储过程,因此您可以使用输出变量。只需使用原始存储过程的结果填充数据集。就像是:

        string sql = "EXEC sp_MyProcedure";
        string connstr = @"data source=MySQLServer;initial catalog=MyDatabase;integrated security=true";
        SqlDataAdapter da = new SqlDataAdapter(sql, connstr);
        DataSet ds = new DataSet();

        da.Fill(ds);
        return ds;