How to get 2 tables in a stored procedure in SQL Server?

Question

SQL

This works great when one result is:

using (var comm = new SqlCommand("dbo.GetProfile", conn))
{
    comm.CommandType = CommandType.StoredProcedure;
    comm.Parameters.Add(new SqlParameter("@uid", uid));
    using (var reader = comm.ExecuteReader())
    {
        if (reader.Read())
        {
            Response.Write(reader["id"]);
            Response.Write(reader["level"]);
        }
    }
}

But when you need to get 2 tables, with a stored procedure like this:

ALTER PROCEDURE [dbo].[GetProfile]
@uid int
AS
BEGIN
    SET NOCOUNT ON;
  SELECT TOP 1 * FROM dbo.users where [email protected];
  SELECT * FROM locations;
END

How to do it? Is this a normal approach or is it better to split it into 2 procedures and 2 requests.

Answer 1

...
        if (reader.Read())
        {
            Response.Write(reader["id"]);
            Response.Write(reader["level"]);
        }

        var ok = reader.NextResult(); // <---

        if( !ok ) return;

        if (reader.Read())
        {
            Response.Write(reader["lid"]);
            Response.Write(reader["sublid"]);
        }
...

Answer 2

What is the purpose of using a stored procedure just to retrieve data? I understand that you would make requests for modification with the addition of logic in order to do all this on the database side, but why fetch? If there are specific reasons, indicate, and we will think about how best to do it.