Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    12

    Unanswered: Return table names - how?

    In a stored procedure returning several tables how can I assign a name to each table.

    I wish to select the table in my app using DataSet.Tables("MyTable") rather than DataSet.Tables(0)

    Create Procedure myProc As

    Select * from table1

    Select * from table2

    Select * from table3

    Go
    -dw

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you mean you only want 1 rs?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    12
    My SP is:

    CREATE PROCEDURE jobListDataSelect

    AS


    SELECT
    CountyName,
    CountyCode,
    ID

    FROM
    counties

    WHERE
    selectiontypeid = 1
    or selectiontypeid=2
    or selectiontypeid=16
    or selectiontypeid=64
    ORDER BY
    countyname

    SELECT
    CountyName + ' ' + Aka As CountyName,
    CountyCode,
    ID

    FROM
    counties

    WHERE
    selectiontypeid = 1
    or selectiontypeid=2
    or selectiontypeid=4
    or selectiontypeid=8

    ORDER BY
    countyname

    GO



    In my code I then allocate the appropriate result (table) to a list control on a windows form as follows:

    Me.cboJobSource.DataSource = _jobListTables.jobListTablesData.Tables(0)

    Me.cboJobDest.DataSource = _jobListTables.jobListTablesData.Tables(1)

    Because the stored proc could be changed in the future to say add another selection an error could be caused and the wrong table assigned to the wrong combo box. This would occur if the select order changed.

    I would prefer to be able to allocate each returned table an "alias" and use:

    Me.cboJobSource.DataSource = _jobListTables.jobListTablesData.Tables("CountyLis tSource")

    Me.cboJobDest.DataSource = _jobListTables.jobListTablesData.Tables("CountyLis tDestination")


    I currently use a class to execute several stored procedures in turn adding the returned table to a DataSet assigning a table name to each which gives me the ability to assign tables to controls as shown above.

    The main reason for what I am trying to do is to consolidate the data selection process into one stored proc and thus speed up the process..
    -dw

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's a more code centric question rather than a db question...

    however...I would go with 3 separate sprocs...

    What's the advatage of having it in 1?

    You could have 1 sproc and pass the table name you want I guess...and us control of flow logic in the sporc....but that's still only 1 rs per call...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    2

    Re: Return table names - how?

    Taking your original example, you could return a value in each select statement to indicate the table name:

    Create Procedure myProc As

    Select *, 'table1' as table_name from table1

    Select *, 'table2' as table_name from table2

    Select *, 'table3' as table_name from table3

    Go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •