Get Multiple Values for one variable output in stored procedure

Question

These are my classes

Public Class Student
{
  Public int Id{get;set;}
  public string Name {get;set;}
}
Public Class Department
{
  Public int Id{get;set;}
  public string Name {get;set;}
  public IList <Student> StudentList{get;set;}
}

These are my tables

Student Table
Id |Name |Department
-----------------
1  |aa   | 1
2  |bb   | 1
3  |cc   | 2

Department Table
ID | Name
----------
1  | xxx
2  | yyy

I need the all data of department with the corresponding list of student by using stored procedure

CREATE PROCEDURE test
(
)
AS
BEGIN
select Id as Id,
       Name as Name,
       ----here I need all the corresponding data from student table as StudentList 
       from Department

Is it possible.. If so Please help me. Apparently I what want is I want to write another procedure and call it for StudentList,Is it possible

Stored Procedures cannot return an object with a List of children. The best you can achieve here, is to return All rows from Student and Department joined by DepartmentID, then on the client side, process the rows and fill the StudentList. — Eugen
– Eugen, Commented Mar 18, 2016 at 7:26
Tag dbms product. Many products have sp's far from ANSI SQL compliant. — jarlh
– jarlh, Commented Mar 18, 2016 at 8:08
You may write two queries to select students and departments as separate tables. And the rest can be done in c# I presume. — Shanid
– Shanid, Commented Mar 18, 2016 at 10:58
yaah that is possible but I just want to know weather is my question possible. and If possible how — Nithila Shanmugananthan
– Nithila Shanmugananthan, Commented Mar 18, 2016 at 12:10

Anonymous Duck · Accepted Answer · 2016-03-18 07:45:29Z

2

Use join, i choose Left Join here so in case if department does not have corresponding students it will still be retrieved.

SELECT d.ID
       ,d.Name
       ,s.ID
       ,s. Name 
FROM Deparment d
LEFT JOIN Student s ON s.Department = d.ID

So from your example the query will return the following result :

ID, Name, Student ID Student Name
1   xxx   1          aa
1   xxx   2          bb
2   yyy   3          cc

Assuming that your stored procedure will accept department id as parameter and retrieve all students under this department, just add a condition in your query

WHERE d.ID = @DeptID

As @Eugen said, just process the result in your application.

edited Mar 18, 2016 at 7:45

answered Mar 18, 2016 at 7:39

Anonymous Duck

3,0081 gold badge14 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nithila Shanmugananthan Over a year ago

If I use join I will be end up with repeating data.I just don't want it,I have edited my question as well

jmoreno · Accepted Answer · 2016-03-20 00:50:09Z

What you describe can be done, but it is not the normal process. You can use for xml to return a column where all of the student information is encoded. Note that while this results in all of the data you want being returned in a single row, you will have to create and populate all of your objects by hand. None of the ORM's like Entity Framework or nHibernate will populate both your department and students. In fact, you will probably end up parsing a string by hand in order to create your students.

The most common practice today would be to use Entity Framework and let it do all of the work for you.

Here's some code to show how you can do as you request. Again, note that this is not recommended, and that it's just one way of doing this:

create table #s (id int, name varchar(max), departmentId int);

insert into #s (id, name, departmentId)
select * from (values  (1  , 'aa', 1) ,(2  , 'bb', 1) ,(3  , 'cc', 2)
) g(id, name, departmentId);

create table #d (departmentId int, name varchar(max));
insert into #d (departmentId, name) select * from (values 
(1  , 'xxx') ,(2  , 'yyy')
)g(departmentId, name)


 select *, (select id, name, departmentId 
            from #s s where s.departmentID = d.departmentId for xml path
           )students
  from #d d 

  drop table #d 
  drop table #s;

Students for deparment 1 =

<row><id>1</id><name>aa</name><departmentId>1</departmentId></row>
<row><id>2</id><name>bb</name><departmentId>1</departmentId></row>

In particular, you may find this a bit heavy, 2016 should be able to do something similar with JSON, but if you'd like that in the meantime, you'll have to customize the output yourself. It can be done, and wouldn't be too difficult.

Collectives™ on Stack Overflow

Get Multiple Values for one variable output in stored procedure

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related