Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: How to return an entire table using store procedure

    hi all


    this is my 2nd post and it's in relation to how to make store procedures return an entire table to a application. i am using .NET as my application development platform.

    I know how parameters can be passed in and out of store procedures, but this returns single value parameters.

    when plain SQL select statements are executed against the database as nonqueries through the ADO/ADO.NET API, an entire recordset/dataset can be returned.

    But when i put the select statements in a store procedure, how do i make the select statement return an entire recordset/dataset back to the calling application???

    thanks for taking your time to read.

    Cheers

    j

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: How to return an entire table using store procedure

    Originally posted by nano_electronix
    hi all


    this is my 2nd post and it's in relation to how to make store procedures return an entire table to a application. i am using .NET as my application development platform.

    I know how parameters can be passed in and out of store procedures, but this returns single value parameters.

    when plain SQL select statements are executed against the database as nonqueries through the ADO/ADO.NET API, an entire recordset/dataset can be returned.

    But when i put the select statements in a store procedure, how do i make the select statement return an entire recordset/dataset back to the calling application???

    thanks for taking your time to read.

    Cheers

    j

    Hi, maybe I did not understand your problem entirely, but a stored procedure returning a recordset is as simple as

    create procedure P as select * from sales
    go

    exec p
    go

    Hope, this helps.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Aug 2003
    Posts
    111
    but wat happens when there are multiple select statements

    how do you retrieve the results of a store procedure that has multiple select statements which would return multiple tables, which means multiple recordsets in the case of asp

    besides given the store procedure you shown there, how would you get it into a recordset/dataset using either asp or asp.net.

    for example, in asp.net i make use of dataadapter to retrieve a table into a dataset (which can store multiple table) but if i use store procedure i am not sure how that can be done.

    cheers

    j

  4. #4
    Join Date
    Aug 2003
    Posts
    111
    i think i might know why you don't understand my problem

    the sql command that you gave is correct and will return a table of results if it is executed within the query analyser.

    wat i am talking about is how would i retrieve the table of results if i were to use the store procedure within an application, how would i retrieve those results into a recordset/dataset (ASP/ASP.NET)

    when i want to execute a storeprocedure using ms .net, wat i have to do is use a oledbcommand and make it of type storeprocedure and then execute it as a nonquery, but i have no idea how the result may be returned to the application when the oledbcommand is executed as a nonquery. i've tried to execute the oledbcommand as a reader rather than a nonquery, but that gave me exception when i tried to read data off the datareader that is returned (i don't think that's the way to do it anyhow).

    I am sure someone would have run across a time when an application need to use a store procedure to execute a batch of sql commands and at the same time returns results to the application as a table of data.

    if i can't do this, the only way that i could achieve the same effect is by creating temporary table and then execute an extra select statement to retrieve the data from the temporary table, this would be quite wasteful.

    Please help

    J

  5. #5
    Join Date
    Jul 2003
    Location
    London
    Posts
    26
    Hi

    Executing SP which will return a record set within .Net is straight forward. By the way for SQL2000 you should use SQLClient name space functions instead of OleDB for performance reason more then anything.

    Use the SQLCommand.ExecuteReader and set the commandtext to the stored procedure and parameters and commandtype = CommandType.StoredProcedure. I enclosed the example from MSDN below for SQL. If you do need OleDB then just change the Sql to OleDb.

    Regards

    Richard...

    SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

    SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);
    salesCMD.CommandType = CommandType.StoredProcedure;

    SqlParameter myParm = salesCMD.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);
    myParm.Value = "Beverages";

    nwindConn.Open();

    SqlDataReader myReader = salesCMD.ExecuteReader();

    Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1));

    while (myReader.Read())
    {
    Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));
    }

    myReader.Close();
    nwindConn.Close();

  6. #6
    Join Date
    Aug 2003
    Posts
    7

    This might help!

    I had the same problem about a month ago. I think what you're looking for is the datareader. Here's an example of how it works:

    Dim connection As New SqlConnection()
    Dim cmdSelect As SqlCommand
    Dim reader As SqlDataReader
    Dim Num As Integer

    Num = 12
    connection = SqlConnection1

    cmdSelect = New SqlCommand("exec sp_getNum @date = " & Num), connection)
    If connection.State <> ConnectionState.Open Then connection.Open()
    reader = cmdSelect.ExecuteReader
    datalist.DataSource = reader
    datalist.DataBind()
    reader.Close()
    connection.Close()

    If you need more tables, just created more readers. Is that what you're looking for?

  7. #7
    Join Date
    Aug 2003
    Posts
    39
    Originally posted by nano_electronix
    but wat happens when there are multiple select statements

    how do you retrieve the results of a store procedure that has multiple select statements which would return multiple tables, which means multiple recordsets in the case of asp
    j
    I would guess you would want to have one select statement per sproc. and then create a recordset for each executed sproc?

  8. #8
    Join Date
    Aug 2003
    Posts
    111
    thanx guys

    I'll try the datareader again, but i have tried it exactly the same way as the sample code above, but like i said it gave me exception, i'll give it another try and get back to you guys, hope it work.

    Originally posted by nano_electronix

    i've tried to execute the oledbcommand as a reader rather than a nonquery, but that gave me exception when i tried to read data off the datareader that is returned (i don't think that's the way to do it anyhow).

    J

  9. #9
    Join Date
    Aug 2003
    Posts
    111
    hi guys


    first of all thanx for very much for all your help. i will sure to come back to this forum for more help later.

    i found out where the problem was, it was a datatype conversion problem that gave me the exception, it wasn't the datareader that gave me the exception, i didn't check the exception carefully.

    i am using oledb over the managed sql components because i want to try to make this application crossplatform for all databases, i haven't had a chance to try oracle yet, but that's where i am heading.

    cheers

    j

Posting Permissions

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