In this post, I will tell you about how to return more than one table from store procedure in SQL Server 2014. Return a single table full of data from store procedure we are able to use a DataTable however to come back multiple tables from store procedure we've got to use DataSet. DataSet could be a bunch of DataTables. so the following code you'll use to return single table. SqlCommand

cmd = new SqlCommand("sp_Login", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = email;
cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = password;
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
   con.Open();
}
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
return dt;

Now write the store procedure like the following code:
CREATE PROCEDURE sp_Test
    @Email nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;
    select * from <tblName> whhere email = @Email
END

And then, It’s time to found how to get multiple table value in one DataSet. Write the code below:
CREATE PROCEDURE sp_Test
AS
BEGIN   
    SET NOCOUNT ON;
    select * from Tbl1
    select * from Tbl2
    select * from Tbl3
    select * from Tbl4
END

And here is the C# code:
SqlCommand cmd = new SqlCommand("sp_test", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{   
con.Open();
}
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
con.Close();
// Retrieving total stored tables from DataSet.             
DataTable dt1 = ds.Tables[0];
DataTable dt1 = ds.Tables[1];
DataTable dt1 = ds.Tables[2];
DataTable dt1 = ds.Tables[3];

I hope it works for you!

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting

Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.