How to return a table through a stored procedure?

Question

_{Wednesday, March 24, 2010 4:00 PM}

Hi,
I'm not so familiar with stored procedures but I'm interesting in moving most of my web selects to stored procedures. I'm working with .net based website and all my data access is something like this:

DataTable dtResult = new DataTable("table");

MySqlConnection connection = new MySqlConnection(STR_CONN);
connection.Open();

MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "SELECT .....";
cmd.Parameters.Add(parameters);
cmd.Connection = connection;

MySqlDataAdapter daAdapter = null;
daAdapter = new MySqlDataAdapter(cmd);
daAdapter.Fill(dtResult);

connection.Close();

return dtResult;

But now I want to improve some process and make tunings. For that I want to use stored procedures, so instead of the "SELECT ...." sentence I'll put stored procedure name.
My problem is how to return a table from one, if you can give me some full example....

All replies (3)

_{Wednesday, March 24, 2010 4:48 PM ✅Answered}

first, you need to create a stored procedure. run this script with your own name and parameters:

CREATE PROCEDURE [dbo].[GetPendingRegistration] 
    @regType as Varchar(20),
    @pendingRegID Uniqueidentifier, 
    @amountPaid Money,
    @ipn as Varchar(2000)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

 THIS IS WHERE YOU HAVE THE SELECT statement based on passed in parameters


END

 

In your code you declare the the datasource as a type of storedProc. Here is an example of doing it in code. you can also declare this in the ASPX file.

Dim sc as SqlDataSource = New SqlDataSource(ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString, _
                            "Exec GetPendingRegistration '' ''")
                        sc.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
                        sc.SelectParameters.Add("regType", regType)
                        sc.SelectParameters.Add("pendingRegID", custom)
                        sc.SelectParameters.Add("amountPaid", amount)
                        sc.SelectParameters.Add("ipn", incomingIpn)
                        
' you can either call this
sc.Select()

' or bind your list view
myListView.DataSource = sc
myListView.DataBind()

 Let me know if this helped

---

_{Wednesday, March 24, 2010 4:54 PM ✅Answered}

Hello!

Your stored procedure needs to return data using a select statement:

CREATE PROCEDURE ReturnCustomersDataTable
(
  @State VARCHAR(2)
)
AS 
    SELECT     
                CustomerId, 
                LastName, 
                FirstName 
    FROM        
                Customers
    WHERE
                State = @State
    ORDER BY 
                LastName + ', ' + FirstName

 

On your visual studio project you can define a class to access the stored procedure:

 

public static class Dac 
{

public static DataTable ExecuteDataTable(string storedProcedureName,  
                                     params SqlParameter[] arrParam)  
{ 
    DataTable dt = new DataTable(); 

    // Open the connection 
    using (SqlConnection cnn = new SqlConnection( 
           "Data Source=.\sqlexpress;Initial Catalog=AcmeRentals; 
                                    Integrated Security=True")) 
    { 
        cnn.Open(); 

        // Define the command 
        using (SqlCommand cmd = new SqlCommand()) 
        { 
            cmd.Connection = cnn; 
            cmd.CommandType = CommandType.StoredProcedure; 
            cmd.CommandText = storedProcedureName; 

            // Handle the parameters 
            if (arrParam != null) 
            { 
                foreach (SqlParameter param in arrParam) 
                    cmd.Parameters.Add(param); 
            } 

            // Define the data adapter and fill the dataset 
            using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 
            { 
                da.Fill(dt); 
            } 
        } 
    } 
    return dt; 
} 
}

 

 

 And you call that code this way:

 

dt = Dac.ExecuteDataTable("ReturnCustomers", new SqlParameter("@State", "TX"));

 

Please take a look at this article by Deborah Kurata, that's where I got the sample code and did some minor adaptations for you.

 

DAL: Retrieve a DataTable using a Stored Procedure
http://msmvps.com/blogs/deborahk/archive/2009/07/07/dal-retrieve-a-datatable-using-a-stored-procedure.aspx

---

_{Friday, March 26, 2010 5:39 PM}

Thanks, here is what I came up with, it doesnt compile..
I didnt tried to fix it too much because I want you to help me improve this proc to be more generic, what this proc do is retrieve 3 random rows from a table. But if now I want to get 20/100/200/... random rows this proc will be very long and not so readble.. I need you to help me make it more generic, for instance - get a parameter that indicate how many rows I want etc... hope you can help me.

DELIMITER $$
DROP PROCEDURE IF EXISTS siteindex.getRandomTags$$
CREATE PROCEDURE siteindex.getRandomTags()
BEGIN
  DECLARE rnd_1 INT DEFAULT 0;
  DECLARE rnd_2 INT DEFAULT 0;
  DECLARE rnd_3 INT DEFAULT 0;

  DECLARE rownum INT DEFAULT 0;

  SELECT COUNT(*)
  INTO rownum
  FROM tagstab;

  set rownum = rownum - 1;

  set rnd_1 = round(rand()*rownum);

  REPEAT
    set rnd_2 = round(rand()*rownum);
  UNTIL rnd_2 != rnd_1
  END REPEAT;

  REPEAT
    set rnd_3 = round(rand()*rownum);
  UNTIL rnd_3 != rnd_1 AND rnd_3 != rnd_2
  END REPEAT;

  SELECT * FROM tagstab LIMIT rnd_1,1
  UNION
  SELECT * FROM tagstab LIMIT rnd_2,1
  UNION
  SELECT * FROM tagstab LIMIT rnd_3,1;


END $$
DELIMITER ;