0

I have a stored procedure in SQL that when it executes, it returns two select queries, so basically 2 tables (see image below)

enter image description here

In my controller, this is the example of how I get the value of the first table return

My controller:

   SqlDataReader dr = cmd.ExecuteReader();

   string column1= "";
   string column2= "";
   string column3= "";
   string column4= "";

   if (dr.Read())
   {
      column1= dr["Column1"].ToString();
      column2= dr["Column2"].ToString();
      column3= dr["Column3"].ToString();
      column4= dr["Column4"].ToString();

   }

   return Json(new { success = true, column1 = column1 , column2 = column2 , column3 = column3 , column4 = column4 }, JsonRequestBehavior.AllowGet);

How to get the result of the 2nd table? I tried like the approach above but it doesn't work and it doesn't return the first table result anymore. Thanks for help

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Lex Aeterna
  • 38
  • 1
  • 6
  • you can go with return one result as out parameter and the other one as select (by the way your code seems c#) – J.Salas May 04 '22 at 06:45
  • 2
    Sounds like you want [`SqlDataReader.NextResult()`](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.nextresult?view=dotnet-plat-ext-6.0) - Slight semantic issue, but a stored procedure can return multiple result sets, but only one [return value](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/return-transact-sql?view=sql-server-ver15). Also, why have you tagged this with JavaScript, it doesn't appear to have anything to do with it? – GarethD May 04 '22 at 06:46
  • this does not look like javascript **at all** – Bravo May 04 '22 at 06:46
  • FYI the `RETURN` value of a procedure has a special meaning; it's not the dataset(s) it returns. A `RETURN` value from a procedure is an `int` value to denote the success of the procedure; `0` for success and anything else for failure. – Thom A May 04 '22 at 07:06
  • Could you please share your `store procedure` so that it can be visualize accordingly. – Md Farid Uddin Kiron May 04 '22 at 07:11
  • As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K May 04 '22 at 07:20
  • Side point: you need `using` blocks to dispose – Charlieface May 04 '22 at 16:55

0 Answers0