locked
How to call two select statements from one stored procedure RRS feed

  • Question

  • User812858302 posted

    Hello Guys,

    i have two dropdownlist as role and username 

    CREATE PROCEDURE `a`.`new_procedure` (
    IN str_data_ct int,
    In str_data_r int

    )
    BEGIN
    select str_data from tbl_parameter_master where num_master_id=3
    and num_id = str_data_ct;

    select name as center_name from b.schools;

    END

    i have to use str_data_r in  role

    and i have to pass str_data_ct in username to populate dropdown.

    pls help

    Monday, September 22, 2014 8:20 AM

Answers

  • User812858302 posted
    DataSet ds = new DataSet();
    
                SqlConnection cn = new SqlConnection("");
                SqlCommand cmd = new SqlCommand("storedprocedurename",cn);
                cmd.CommandType = CommandType.StoredProcedure;
                //add parameters if you have
    
                SqlDataAdapter da = new SqlDataAdapter(cmd);
    
                da.Fill(ds);now ds is with 3 Table;
    
                ddl1.DataSource=ds.Tables[0];
                ddl1.DataValueField="Table1ID";
                ddl1.DataTextField="Table1Name";
                ddl1.DataBind();
    
                
                ddl2.DataSource=ds.Tables[1];
                ddl2.DataValueField="Table2ID";
                ddl2.DataTextField="Table2Name";
                ddl2.DataBind();
                
                ddl3.DataSource=ds.Tables[2];
                ddl3.DataValueField="Table3ID";
                ddl3.DataTextField="Table3Name";
                ddl3.DataBind();

    Hey guys thanx for ur replies actually i want to call 3 statements from one stored procedure for dropdowns and i got the solution

    for others you can refer http://forums.asp.net/t/2011143.aspx?how+to+populate+dropdown

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 29, 2014 5:43 AM

All replies

  • User77042963 posted

    You need to make  one select work first:

    CREATE PROCEDURE dbo.new_procedure1 (
    @str_data_ct int 
    
    )
    BEGIN
    
    SET NOCOUNT ON;
    
    select str_data from tbl_parameter_master 
    where num_master_id=3
    and num_id = @str_data_ct;
    
    
    
    END

    Monday, September 22, 2014 8:56 AM
  • User812858302 posted

    hey i am creating stored procedure in mysql workbench,

    and in that for

    SET NOCOUNT ON;
    i m getting error
    Tuesday, September 23, 2014 1:01 AM
  • User-578610739 posted

    Hi Dear,

    I don't know the syntax of mysql, but I guide you. You can do it by 2 option.

    1. You can get the both the result in front-end side i.e. c# or VB side, where you can get by the loop to bind drop-down.

    dd1.datasource = ds.tables[0];

    dd2.datasource = ds.tables[1];

    2. Second thing, you can add one parameter (which tells procedure to which data to fetch) in your sp, for each drop down , procedure only return for that dropdown result like

    create procedure procedurename
    (
    	parameter list
    	flag varchar(50) = 'username'  --default value, if null pass
    )
    as
    begin
      if (flag = 'username ')
       begin
    		select str_data from tbl_parameter_master where num_master_id=3 and num_id = str_data_ct;
       end
       else
       begin
    		select name as center_name from b.schools;
       end
    end

    Tuesday, September 23, 2014 7:07 AM
  • User-271186128 posted

    Hi Surya18,

    SET NOCOUNT ON;
    i m getting error

    The SET NOCOUNT ON stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

    From my point of view, MySQL doesn't report the number of rows affected by a query, therefore there's no such function.

    Here are some articles, perhaps they can help you.

    Calling Stored Procedures with Statement Objects

    MySQL Stored Procedure with Multiple Select statements From Different Tables

    Best Regards,
    Dillion

    Monday, September 29, 2014 5:13 AM
  • User812858302 posted
    DataSet ds = new DataSet();
    
                SqlConnection cn = new SqlConnection("");
                SqlCommand cmd = new SqlCommand("storedprocedurename",cn);
                cmd.CommandType = CommandType.StoredProcedure;
                //add parameters if you have
    
                SqlDataAdapter da = new SqlDataAdapter(cmd);
    
                da.Fill(ds);now ds is with 3 Table;
    
                ddl1.DataSource=ds.Tables[0];
                ddl1.DataValueField="Table1ID";
                ddl1.DataTextField="Table1Name";
                ddl1.DataBind();
    
                
                ddl2.DataSource=ds.Tables[1];
                ddl2.DataValueField="Table2ID";
                ddl2.DataTextField="Table2Name";
                ddl2.DataBind();
                
                ddl3.DataSource=ds.Tables[2];
                ddl3.DataValueField="Table3ID";
                ddl3.DataTextField="Table3Name";
                ddl3.DataBind();

    Hey guys thanx for ur replies actually i want to call 3 statements from one stored procedure for dropdowns and i got the solution

    for others you can refer http://forums.asp.net/t/2011143.aspx?how+to+populate+dropdown

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 29, 2014 5:43 AM