We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

ASP.Net SQL Server Stored Procedure

vad2319
vad2319 asked
on
Medium Priority
853 Views
Last Modified: 2008-02-26
Hi Experts,

Consider the Stored Procedure,

…………..
………….

Select * from Table1

Select Count(*) from Table2

GO

…………………..

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

Thanks,

Comment
Watch Question

SQL Server Architect
CERTIFIED EXPERT
Commented:
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);

da.Fill(ds);
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thank you Experts. Nauman, your answer was also very nice, but dbaduck's answer was what I was looking for.
Thanks again.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.