CODEWITHSUNDEEP
sql-server
user2465036
user2465036

Reputation: 351

Returning result set from another stored procedure

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.

Upvotes: 0

Views: 551

Answers (1)

Brian Pressler
Brian Pressler

Reputation: 6713

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;

Upvotes: 1

Related Questions