Stored Procedure Multiple Tables

Discussion in 'ASP .Net' started by Chris, Jul 1, 2007.

  1. Chris

    Chris Guest

    This might be a stupid question.... I have a stored procedures, which uses
    two selects. When I run the SP I get two resultsets, one very big and the
    other much smaller, which is the one I want in the dataset. How do a ensure
    only the last one goes in the dataset.
    Chris, Jul 1, 2007
    #1
    1. Advertising

  2. Chris

    Mark Rae Guest

    "Chris" <> wrote in message
    news:...

    > This might be a stupid question.... I have a stored procedure, which uses
    > two selects. When I run the SP I get two resultsets, one very big and the
    > other much smaller, which is the one I want in the dataset. How do a
    > ensure only the last one goes in the dataset.


    Well, firstly do you need the SP to return both resultsets? If not, get rid
    of the one you don't need... :)

    If you do, ignore the one you don't need in the DataSet.

    E.g., if you are trying to bind the smaller of the two resultsets into a
    GridView, you could do something like this:

    DataSet MyDS = <run your SP>;
    MyGridView.DataSource = MyDS.Tables[1];
    MyGridView.DataBind();


    --
    http://www.markrae.net
    Mark Rae, Jul 1, 2007
    #2
    1. Advertising

  3. To Add to what Mark has suggested, use an extra parameter to the Stored
    procedure whether to do the first select statement using a T-Sql if
    condition. If we do so we can avoid populating the dataset with unwanted
    data. Binding to the Gridview would now be as follows as we would expect only
    one resultset from the stored proc:

    DataSet MyDS = <run your SP>;
    MyGridView.DataSource = MyDS.Tables[0];
    MyGridView.DataBind();

    HTH
    Siva


    "Mark Rae" wrote:

    > "Chris" <> wrote in message
    > news:...
    >
    > > This might be a stupid question.... I have a stored procedure, which uses
    > > two selects. When I run the SP I get two resultsets, one very big and the
    > > other much smaller, which is the one I want in the dataset. How do a
    > > ensure only the last one goes in the dataset.

    >
    > Well, firstly do you need the SP to return both resultsets? If not, get rid
    > of the one you don't need... :)
    >
    > If you do, ignore the one you don't need in the DataSet.
    >
    > E.g., if you are trying to bind the smaller of the two resultsets into a
    > GridView, you could do something like this:
    >
    > DataSet MyDS = <run your SP>;
    > MyGridView.DataSource = MyDS.Tables[1];
    > MyGridView.DataBind();
    >
    >
    > --
    > http://www.markrae.net
    >
    >
    =?Utf-8?B?U2l2YWt1bWFyIEc=?=, Jul 1, 2007
    #3
  4. Chris

    sloan Guest

    My suggestion (which is a alternate of the flag being sent is ) is:


    Create 2 procedures.


    uspEmployeeGetAll
    Select EmpID, LastName, FirstName from dbo.Employee


    uspDepartmentGetAll
    Select DeptID, DepartmentName from dbo.Department

    Then a wrapper usp


    uspEmployeesAndDepartmentsGetAll
    EXEC dbo.uspEmployeeGetAll
    EXEC dbo.uspDepartmentGetAll



    That'll work.


    If you use an IDataReader, you have more control over loading data. But you
    pay for it with more code.

    See my blog for an example:
    http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry



    "Chris" <> wrote in message
    news:...
    > This might be a stupid question.... I have a stored procedures, which uses
    > two selects. When I run the SP I get two resultsets, one very big and the
    > other much smaller, which is the one I want in the dataset. How do a

    ensure
    > only the last one goes in the dataset.
    >
    >
    sloan, Jul 3, 2007
    #4
  5. Mark Rae wrote:
    > "Chris" <> wrote in message
    > news:...
    >
    >> This might be a stupid question.... I have a stored procedure, which
    >> uses two selects. When I run the SP I get two resultsets, one very big
    >> and the other much smaller, which is the one I want in the dataset.
    >> How do a ensure only the last one goes in the dataset.

    >
    > Well, firstly do you need the SP to return both resultsets? If not, get
    > rid of the one you don't need... :)
    >
    > If you do, ignore the one you don't need in the DataSet.
    >
    > E.g., if you are trying to bind the smaller of the two resultsets into a
    > GridView, you could do something like this:
    >
    > DataSet MyDS = <run your SP>;
    > MyGridView.DataSource = MyDS.Tables[1];
    > MyGridView.DataBind();
    >
    >

    An additional benefit of doing this is that it will even run faster! :)

    --
    http://bytes.thinkersroom.com
    Rad [Visual C# MVP], Jul 3, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jon Spivey
    Replies:
    3
    Views:
    9,631
    Jon Spivey
    Feb 1, 2004
  2. Patrick Olurotimi Ige
    Replies:
    2
    Views:
    507
    Eliyahu Goldin
    May 9, 2005
  3. Lex
    Replies:
    3
    Views:
    7,371
    jason
    Dec 30, 2005
  4. Mike P
    Replies:
    0
    Views:
    3,295
    Mike P
    Jun 19, 2006
  5. stjulian
    Replies:
    1
    Views:
    185
    Bob Barrows [MVP]
    Nov 1, 2006
Loading...

Share This Page