locked
Result of SP whit 3 select RRS feed

  • Question

  • User-1687766116 posted

    Hi,

    I have a SP whit 3 result, but i want show only last result,

    how i do?

    select 1
    select 2
    select 3

    I want only show select 3 result,

    and in select 3 i use select 1 and select 2,

    Please help me, 

    thanks

    Thursday, March 31, 2016 3:14 PM

All replies

  • User-821857111 posted

    If you are using a DataReader to obtain the result, you can use the NextResult method to advance from one resultset to the next. If you call it twice, you will be on the third result.

    Thursday, March 31, 2016 3:52 PM
  • User77042963 posted

    You can combine these three sets with UNION ALL:

    select col1,col2,col3, 1 as src from t1
    UNION ALL
    select col1,col2,col3, 2 as src  from t2
    UNION ALL
    select col1,col2,col3, 3 as src  from t3

    Thursday, March 31, 2016 6:45 PM
  • User-1687766116 posted

    my SP is:

    SELECT        dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, SUM(dbo.card_out.out1) AS out1, dbo.card_out.type1, dbo.office1.id_office
    FROM            dbo.region INNER JOIN
                             dbo.office1 ON dbo.region.id_reg = dbo.office1.id_reg INNER JOIN
                             dbo.card_out ON dbo.office1.id_office = dbo.card_out.id_office INNER JOIN
                             dbo.card1 ON dbo.card_out.id_card = dbo.card1.id_card
    WHERE        (dbo.card_out.datefa1 BETWEEN 13950101 AND 13950114) AND (dbo.card_out.type1 = 2)
    GROUP BY dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, dbo.card_out.type1, dbo.office1.id_office
    
    
    
    SELECT        dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, SUM(dbo.card_out.out1) AS mas, dbo.card_out.type1, dbo.office1.id_office
    FROM            dbo.region INNER JOIN
                             dbo.office1 ON dbo.region.id_reg = dbo.office1.id_reg INNER JOIN
                             dbo.card_out ON dbo.office1.id_office = dbo.card_out.id_office INNER JOIN
                             dbo.card1 ON dbo.card_out.id_card = dbo.card1.id_card
    WHERE        (dbo.card_out.datefa1 BETWEEN 13950101 AND 13950115) AND (dbo.card_out.type1 = 3)
    GROUP BY dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, dbo.card_out.type1, dbo.office1.id_office
    
    
    
    SELECT        dbo.v_out1.name_reg, dbo.v_out1.name_office, dbo.v_out1.id_card, dbo.v_out1.name_card, dbo.v_out1.out1, dbo.v_out1.type1, dbo.v_out1.id_office, dbo.v_mas.mas, 
                             dbo.v_out1.out1 - dbo.v_mas.mas AS Expr1
    FROM            dbo.v_mas INNER JOIN
                             dbo.v_out1 ON dbo.v_mas.id_office = dbo.v_out1.id_office AND dbo.v_mas.id_card = dbo.v_out1.id_card

    i want only show bold result, how i do?

    no needs for show first and second select result

    Thursday, March 31, 2016 7:37 PM
  • User-1687766116 posted

    If you are using a DataReader to obtain the result, you can use the NextResult method to advance from one resultset to the next. If you call it twice, you will be on the third result.

    i use sqldatasource

    Thursday, March 31, 2016 7:44 PM
  • User77042963 posted

    I don't see how your third query is using the previous two SELECT queries.

    And you need only one query for your first two queries:

    SELECT        dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, SUM(dbo.card_out.out1) AS out1, dbo.card_out.type1, dbo.office1.id_office
    FROM            dbo.region INNER JOIN
                             dbo.office1 ON dbo.region.id_reg = dbo.office1.id_reg INNER JOIN
                             dbo.card_out ON dbo.office1.id_office = dbo.card_out.id_office INNER JOIN
                             dbo.card1 ON dbo.card_out.id_card = dbo.card1.id_card
    WHERE        (dbo.card_out.datefa1 BETWEEN 13950101 AND 13950114) AND (dbo.card_out.type1 IN ( 2, 3))
    GROUP BY dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, dbo.card_out.type1, dbo.office1.id_office

    Thursday, March 31, 2016 8:40 PM
  • User-1687766116 posted

    Is there a way to show only the last  Select?

    my SP return 3 Result, But I want show only last select result,

    i want show only green area,

    please help me

    Thursday, March 31, 2016 8:52 PM
  • User77042963 posted

    If you don't need them, comment them out and keep only the one you need.

    Thursday, March 31, 2016 9:23 PM
  • User-1687766116 posted

    If you don't need them, comment them out and keep only the one you need.

    i use first and second select in last select

    Thursday, March 31, 2016 9:24 PM
  • User-1687766116 posted

    is the no way, how to merge this query to one select?

    SELECT        dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, SUM(dbo.card_out.out1) AS out1, dbo.card_out.type1, dbo.office1.id_office
    FROM            dbo.region INNER JOIN
                             dbo.office1 ON dbo.region.id_reg = dbo.office1.id_reg INNER JOIN
                             dbo.card_out ON dbo.office1.id_office = dbo.card_out.id_office INNER JOIN
                             dbo.card1 ON dbo.card_out.id_card = dbo.card1.id_card
    WHERE        (dbo.card_out.datefa1 BETWEEN 13950101 AND 13950114) AND (dbo.card_out.type1 = 2)
    GROUP BY dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, dbo.card_out.type1, dbo.office1.id_office 
    
    
    
    SELECT        dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, SUM(dbo.card_out.out1) AS mas, dbo.card_out.type1, dbo.office1.id_office
    FROM            dbo.region INNER JOIN
                             dbo.office1 ON dbo.region.id_reg = dbo.office1.id_reg INNER JOIN
                             dbo.card_out ON dbo.office1.id_office = dbo.card_out.id_office INNER JOIN
                             dbo.card1 ON dbo.card_out.id_card = dbo.card1.id_card
    WHERE        (dbo.card_out.datefa1 BETWEEN 13950101 AND 13950115) AND (dbo.card_out.type1 = 3)
    GROUP BY dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, dbo.card_out.type1, dbo.office1.id_office
    
    
    
    SELECT        dbo.v_out1.name_reg, dbo.v_out1.name_office, dbo.v_out1.id_card, dbo.v_out1.name_card, dbo.v_out1.out1, dbo.v_out1.type1, dbo.v_out1.id_office, dbo.v_mas.mas, 
                             dbo.v_out1.out1 - dbo.v_mas.mas AS Expr1
    FROM            dbo.v_mas INNER JOIN
                             dbo.v_out1 ON dbo.v_mas.id_office = dbo.v_out1.id_office AND dbo.v_mas.id_card = dbo.v_out1.id_card

    Thursday, March 31, 2016 9:26 PM
  • User-219423983 posted

    Hi mehr_83,

    Is there a way to show only the last  Select?

    my SP return 3 Result, But I want show only last select result,

    i want show only green area,

    i use first and second select in last select

    In the above image you provided, it’s not easy to understand how the first and second select in the last one, you’d better show more things to explain it.

    If you just want to the “green” result, you could just execute the third SQL query with commenting out the first two queries to get the result as suggested above.

    is the no way, how to merge this query to one select?

    For this, you could use Union as suggested by limno to combine more results into one. But, there are two basic rules for combining the result sets of two queries by using UNION as below and you should pay attention to.

        •The number and the order of the columns must be the same in all queries.

        •The data types must be compatible.

    Best Regards,

    Weibo Zhang

    Friday, April 1, 2016 2:07 AM
  • User-1687766116 posted

    for this,

    i use 2 SP, 

    but for send parameter to first SP, result of first SP is show, i want only pass parameter to first SP Whiteout showing result,

    exec re1 13950101
    
    SELECT        dbo.v_out1.name_reg, dbo.v_out1.name_office, dbo.v_out1.id_card, dbo.v_out1.name_card, dbo.v_out1.out1, dbo.v_out1.type1, dbo.v_out1.id_office, dbo.v_mas.mas, 
                             dbo.v_out1.out1 - dbo.v_mas.mas AS Expr1
    FROM            dbo.v_mas INNER JOIN
                             dbo.v_out1 ON dbo.v_mas.id_office = dbo.v_out1.id_office AND dbo.v_mas.id_card = dbo.v_out1.id_card

    i want only pass parameter to use in SP, I want only show result of select (bold)

    Friday, April 1, 2016 6:19 AM
  • User-821857111 posted

    Mikesdotnetting

    If you are using a DataReader to obtain the result, you can use the NextResult method to advance from one resultset to the next. If you call it twice, you will be on the third result.

    i use sqldatasource

    You can't use NextResult with a SqlDataSource. You can use code behind instead. Here's how you might do it if you were binding the result to a GridView. Both the connection string name and the sp name are "Test" in this example. You need to change those to suit your needs:

    protected void Page_Load(object sender, EventArgs e)
    {
        using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
        using (var cmd = new SqlCommand("Test", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            conn.Open();
            using (var rdr = cmd.ExecuteReader())
            {
                rdr.NextResult();
                rdr.NextResult();
                GridView1.DataSource = rdr;
                GridView1.DataBind();
            }
        }
    }

    Friday, April 1, 2016 6:57 AM
  • User-1687766116 posted

    thanks, but i need this for SQL server

    Friday, April 1, 2016 5:28 PM
  • User77042963 posted

    mehr_83

    is the no way, how to merge this query to one select?

    SELECT        dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, SUM(dbo.card_out.out1) AS out1, dbo.card_out.type1, dbo.office1.id_office
    FROM            dbo.region INNER JOIN
                             dbo.office1 ON dbo.region.id_reg = dbo.office1.id_reg INNER JOIN
                             dbo.card_out ON dbo.office1.id_office = dbo.card_out.id_office INNER JOIN
                             dbo.card1 ON dbo.card_out.id_card = dbo.card1.id_card
    WHERE        (dbo.card_out.datefa1 BETWEEN 13950101 AND 13950114) AND (dbo.card_out.type1 = 2)
    GROUP BY dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, dbo.card_out.type1, dbo.office1.id_office 
    
    
    
    SELECT        dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, SUM(dbo.card_out.out1) AS mas, dbo.card_out.type1, dbo.office1.id_office
    FROM            dbo.region INNER JOIN
                             dbo.office1 ON dbo.region.id_reg = dbo.office1.id_reg INNER JOIN
                             dbo.card_out ON dbo.office1.id_office = dbo.card_out.id_office INNER JOIN
                             dbo.card1 ON dbo.card_out.id_card = dbo.card1.id_card
    WHERE        (dbo.card_out.datefa1 BETWEEN 13950101 AND 13950115) AND (dbo.card_out.type1 = 3)
    GROUP BY dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, dbo.card_out.type1, dbo.office1.id_office
    
    
    
    SELECT        dbo.v_out1.name_reg, dbo.v_out1.name_office, dbo.v_out1.id_card, dbo.v_out1.name_card, dbo.v_out1.out1, dbo.v_out1.type1, dbo.v_out1.id_office, dbo.v_mas.mas, 
                             dbo.v_out1.out1 - dbo.v_mas.mas AS Expr1
    FROM            dbo.v_mas INNER JOIN
                             dbo.v_out1 ON dbo.v_mas.id_office = dbo.v_out1.id_office AND dbo.v_mas.id_card = dbo.v_out1.id_card
    ;with mycte1 as (
    SELECT        dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, SUM(dbo.card_out.out1) AS out1, dbo.card_out.type1, dbo.office1.id_office
    FROM            dbo.region INNER JOIN
                             dbo.office1 ON dbo.region.id_reg = dbo.office1.id_reg INNER JOIN
                             dbo.card_out ON dbo.office1.id_office = dbo.card_out.id_office INNER JOIN
                             dbo.card1 ON dbo.card_out.id_card = dbo.card1.id_card
    WHERE        (dbo.card_out.datefa1 BETWEEN 13950101 AND 13950114) AND (dbo.card_out.type1 = 2)
    GROUP BY dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, dbo.card_out.type1, dbo.office1.id_office 
    )
    ,mycte2 as (
    
    
    
    SELECT        dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, SUM(dbo.card_out.out1) AS mas, dbo.card_out.type1, dbo.office1.id_office
    FROM            dbo.region INNER JOIN
                             dbo.office1 ON dbo.region.id_reg = dbo.office1.id_reg INNER JOIN
                             dbo.card_out ON dbo.office1.id_office = dbo.card_out.id_office INNER JOIN
                             dbo.card1 ON dbo.card_out.id_card = dbo.card1.id_card
    WHERE        (dbo.card_out.datefa1 BETWEEN 13950101 AND 13950115) AND (dbo.card_out.type1 = 3)
    GROUP BY dbo.region.name_reg, dbo.office1.name_office, dbo.card_out.id_card, dbo.card1.name_card, dbo.card_out.type1, dbo.office1.id_office)
    
    ,mycte3 as (
    
    SELECT        dbo.v_out1.name_reg, dbo.v_out1.name_office, dbo.v_out1.id_card, dbo.v_out1.name_card, dbo.v_out1.out1, dbo.v_out1.type1, dbo.v_out1.id_office
    , dbo.v_mas.mas,  dbo.v_out1.out1 - dbo.v_mas.mas AS Expr1
    FROM            dbo.v_mas INNER JOIN
                             dbo.v_out1 ON dbo.v_mas.id_office = dbo.v_out1.id_office AND dbo.v_mas.id_card = dbo.v_out1.id_card
    						 )
    
    
    ,cteFinal AS (
     Select name_reg,  name_office,  id_card,  name_card,   mas,  type1,  id_office, null as  mas, null Expr1
    FROM   from mycte1 
     Union ALL
      Select name_reg,  name_office,  id_card,  name_card,   out1,  type1,  id_office, null as  mas, null Expr1
    FROM   from mycte2 
     Union ALL
      Select name_reg,  name_office,  id_card,  name_card,   out1,  type1,  id_office,   mas,  Expr1
    FROM   from mycte3
    )
    
    Select name_reg,  name_office,  id_card,  name_card,   out1,  type1,  id_office,   mas,  Expr1 
    from cteFinal


    Friday, April 1, 2016 5:52 PM
  • User-1687766116 posted

    thnaks Limno, but for 

    ,cteFinal (
     Select name_reg,  name_office,  id_card,  name_card,   mas,  type1,  id_office, null as  mas, null Expr1
    FROM   from mycte1 
     Union ALL
      Select name_reg,  name_office,  id_card,  name_card,   out1,  type1,  id_office, null as  mas, null Expr1
    FROM   from mycte2 
     Union ALL
      Select name_reg,  name_office,  id_card,  name_card,   out1,  type1,  id_office,   mas,  Expr1
    FROM   from mycte3
    )

    error:

    Msg 156, Level 15, State 1, Line 32
    Incorrect syntax near the keyword 'Select'.
    Msg 156, Level 15, State 1, Line 33
    Incorrect syntax near the keyword 'from'.
    Msg 156, Level 15, State 1, Line 36
    Incorrect syntax near the keyword 'from'.
    Msg 156, Level 15, State 1, Line 39
    Incorrect syntax near the keyword 'from'.

    I get error

    Friday, April 1, 2016 5:56 PM
  • User77042963 posted
    ,cteFinal AS ( . ..)
    Friday, April 1, 2016 6:42 PM
  • User-1687766116 posted
    ,cteFinal AS (
     Select name_reg,  name_office,  id_card,  name_card,   mas,  type1,  id_office, null as  mas, null Expr1
    FROM   from mycte1 
     Union ALL
      Select name_reg,  name_office,  id_card,  name_card,   out1,  type1,  id_office, null as  mas, null Expr1
    FROM   from mycte2 
     Union ALL
      Select name_reg,  name_office,  id_card,  name_card,   out1,  type1,  id_office,   mas,  Expr1
    FROM   from mycte3
    )
    
    Select name_reg,  name_office,  id_card,  name_card,   out1,  type1,  id_office,   mas,  Expr1 
    from cteFinal

    Incorrect syntax near the keyword 'from'.

    Friday, April 1, 2016 7:27 PM
  • User-219423983 posted

    Hi mehr_83,

    I suggest you’d better first read this article to learn how to debug the T-SQL in SQL Server Management Studio, then you could try to find out the issue by yourself. In the above issue, you could have a try to change the “mas” to “out1” in the select query of “mycte1” in the “cteFinal”.

    Best Regards,

    Weibo Zhang

    Monday, April 4, 2016 9:02 AM