Returning result set from another stored procedure

Question

Technical Environment: ASP.NET and 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.

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.

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.

Why can't you just use the second stored procedure directly? — Zohar Peled, May 07 '15 at 20:01
Looping and cursors in sql have a particularly bad code smell. That is because looping in sql in very bad for performance. Almost anything in sql can be rewritten in a set based manner. The advantage is that is easier to write, performs faster and is easier to maintain. The challenge is that it requires a different mindset. You have to think in sets instead of rows and that is not an easy change to make. The way you have this coded it will only return the value for the last pass through your cursor. — Sean Lange, May 07 '15 at 20:01
You can't return a resultset as an OUTPUT parameter, you have to return it as a resultset. — Tab Alleman, May 07 '15 at 20:21
Possible duplicate of [http://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure](http://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure) — John Odom, May 07 '15 at 20:25

score 1 · Answer 1 · answered May 07 '15 at 21:43

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;

Returning result set from another stored procedure

1 Answers1