stored procedure call in another sp

I have one sp

CREATE PROCEDURE [dbo].[GetDistinctPageNumber]

AS
BEGIN

   select Distinct PageNumber from MyTable order by PageNumber asc

END

which returns number from 1 to 22 as output.

Now, my requirement is get output from table on the basis of these pagenumber one by one in another sp

suppose pagenumber is 1 is pass through c# code than get all rows and columns which has pagenumber 1.

please help asap.

Check the following code to fullfill your requirement. Lets say here is your second storeprocedure which returns data on the basis on PageNumber:

CREATE PROCEDURE [dbo].[GetPageInfo]
@PageNumber INT
AS
BEGIN
SELECT * FROM PageInfo
WHERE PageNumber = @PageNumber
END

Here is the CSharp code which get pageInfo data:

SqlConnection sqlConn = new SqlConnection("YourConnectionString");
SqlCommand cmd = new SqlCommand("GetDistinctPageNumber", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
sqlConn.Open();
DataTable dtPageNumbers = new DataTable();
DataTable dtPageInfo;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dtPageNumbers);
if (dtPageNumbers != null && dtPageNumbers.Rows.Count > 0)
{
foreach (DataRow row in dtPageNumbers.Rows)
{
dtPageInfo = new DataTable();
int pageNumber = Convert.ToInt32(row["PageNumber"]);
cmd = new SqlCommand("GetPageInfo", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PageNumber", pageNumber);
da.SelectCommand = cmd;
da.Fill(dtPageInfo);
// TODO dtPageInfo will contain PageInfo
}
}
}
catch (Exception)
{
throw;
}
finally
{
if (sqlConn != null && sqlConn.State != ConnectionState.Closed)
{
sqlConn.Close();
}
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Muhammad Imran Ansari4y ago
@Ritu,
Below code is working for you? Or you need any help on this?
Muhammad Imran Ansari4y ago
Check the following code to fullfill your requirement. Lets say here is your second storeprocedure which returns data on the basis on PageNumber:
CREATE PROCEDURE [dbo].[GetPageInfo]
@PageNumber INT
AS
BEGIN
    SELECT * FROM PageInfo
    WHERE PageNumber = @PageNumber
END
Here is the CSharp code which get pageInfo data:
SqlConnection sqlConn = new SqlConnection("YourConnectionString");
SqlCommand cmd = new SqlCommand("GetDistinctPageNumber", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
    sqlConn.Open();
    DataTable dtPageNumbers = new DataTable();
    DataTable dtPageInfo;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(dtPageNumbers);
    if (dtPageNumbers != null && dtPageNumbers.Rows.Count > 0)
    {
        foreach (DataRow row in dtPageNumbers.Rows)
        {
            dtPageInfo = new DataTable();
            int pageNumber = Convert.ToInt32(row["PageNumber"]);
            cmd = new SqlCommand("GetPageInfo", sqlConn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageNumber", pageNumber);
            da.SelectCommand = cmd;
            da.Fill(dtPageInfo);
            // TODO dtPageInfo will contain PageInfo
        }
    }
}
catch (Exception)
{
    throw;
}
finally
{
    if (sqlConn != null && sqlConn.State != ConnectionState.Closed)
    {
        sqlConn.Close();
    }
}