ASP.Net SQL Server Stored Procedure

Posted on 2006-03-29
Last Modified: 2008-02-26
Hi Experts,

Consider the Stored Procedure,


Select * from Table1

Select Count(*) from Table2



Now, this Stored Procedure is returning 2 values ( DataTable and the Count), hence how could this be handled in the .Net Application.
I have read on some websites that, a stored Procedure is like a function and can only return one value/datatable.

Does the above the Stored Procedure return 2 datatables?

Please help me clarify, how could I use the above Stored Procedure in the front-end like C#/VB.Net


Question by:vad2319
    LVL 25

    Expert Comment

    LVL 24

    Accepted Solution

    The questions listed above don't seem to address your issue.

    The thing you need to know is depending on what you want to use to get the data back.

    The bottom line is that when you have a stored procedure return 2 rowsets, which is what you are doing in your example, is that these are some of your options.

    DataSet ds = new DataSet();

    SqlConnection sql = new SqlConnection(connectionstring);
    SqlCommand cmd = new SqlCommand("dbo.somestoredprocedure", sql);
    SqlDataAdapter da = new SqlDataAdapter(cmd);

    if(da.Tables.Count > 1)
        // Table 1 is ds.Tables[0] and Table 2 is ds.Tables[1].

    If you use a DataReader then you can use the DataReader.NextResultSet() to get the next result into place, then you can Read just like any resultset.

    Let me know if you need any more information.
    Ben Miller

    Author Comment

    Thank you Experts. Nauman, your answer was also very nice, but dbaduck's answer was what I was looking for.
    Thanks again.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Effect on button 11 40
    HTML table 7 39
    SequenceEqual in C# List 2 32
    Pass selected value to a function 14 27
    AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
    I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now