locked
multiples select in a procedure RRS feed

  • Question

  • User-1332855729 posted

    Hi, 

    I'm trying to load the result returned from a stored procedure into a dataset to use with ASP.

    My procedure is

    create procedure get info 
    (@employee id int)
    AS
    select firstname, lastname from employee where employeeid = @employeeid
    
    Select job, salary from Job where employeeid = @employeeid

    I would like to get only the column job and salary to put in my dataset but it looks like the procedure doesn't recognize them because it runs two queries. 

    How can get job and salary from this procedure ?

    Thanks

    Wednesday, November 4, 2015 4:32 PM

Answers

  • User2103319870 posted

    You can do a InnerJoin with Employee and Job table based on employeeid and then get the details

    Sample Query

    CREATE PROCEDURE get_info 
    (@employee id int)
    AS
    SELECT emp.firstname, emp.lastname,jo.job,jo.salary FROM employee emp
    INNER JOIN Job jo on jo.employeeid = emp.employeeid
    WHERE emp.employeeid = @employeeid
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 4, 2015 6:34 PM
  • User-1716253493 posted

    maybe you can get table job like this

    DataTable dtjob = ds.Tables["job"];
    DataTable dtemployee = ds.Tables["employee"];

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 4, 2015 7:52 PM
  • User1644755831 posted

    Hello httsqv,

    You need to use data adapter to fill dataset that will fill the dataset with multiple resultsets into the table.

    https://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.110).aspx

    http://stackoverflow.com/questions/7239450/returning-multiple-tables-from-a-stored-procedure

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 5, 2015 8:32 PM
  • User77042963 posted

    By keeping your stored procedure to do simple thing is easy to maintain. You can create two separate stored procedures with proper names. If you do want to combine information from multiple tables , use JOINS to get the relevant result.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2015 9:58 AM

All replies

  • User2103319870 posted

    You can do a InnerJoin with Employee and Job table based on employeeid and then get the details

    Sample Query

    CREATE PROCEDURE get_info 
    (@employee id int)
    AS
    SELECT emp.firstname, emp.lastname,jo.job,jo.salary FROM employee emp
    INNER JOIN Job jo on jo.employeeid = emp.employeeid
    WHERE emp.employeeid = @employeeid
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 4, 2015 6:34 PM
  • User-1332855729 posted

    Hi a2h, your result is returning one execution, I would like keep multiples executions (this is just an example) and still be able to access the wanted columns in the last execution

    Wednesday, November 4, 2015 6:55 PM
  • User-1716253493 posted

    maybe you can get table job like this

    DataTable dtjob = ds.Tables["job"];
    DataTable dtemployee = ds.Tables["employee"];

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 4, 2015 7:52 PM
  • User1644755831 posted

    Hello httsqv,

    You need to use data adapter to fill dataset that will fill the dataset with multiple resultsets into the table.

    https://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.110).aspx

    http://stackoverflow.com/questions/7239450/returning-multiple-tables-from-a-stored-procedure

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 5, 2015 8:32 PM
  • User77042963 posted

    By keeping your stored procedure to do simple thing is easy to maintain. You can create two separate stored procedures with proper names. If you do want to combine information from multiple tables , use JOINS to get the relevant result.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2015 9:58 AM