How to get multiple list of arrays from stored procedures.

Sivakumar

9y934Visual Basic .NET

Hi,

I have three tables Sessions, SessionSpeakerMapping, Speakers .

SessionSpeakerMapping table contains SessionId and SpeakerId.

I created get sessions procedure.

I want to get list of speakers related to particular session.

Please give me any suggestions.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Prakash Kumar9y ago
Why your using TrackType and Room in your Query i dont know,

Check the Below query
select * from Speakers
where id in
(selct SpeakerId from SessionSpeakerMapping SSM innerjoin Sessions S on SSM.SessionId =s.Id)
It will return the exact answer, change as per your need
0
Sivakumar9y ago
ALTER PROCEDURE [dbo].[GetSessions]

AS
BEGIN
SET NOCOUNT ON;
SELECT SessionID,[Date],[StartTime],[EndTime],concat(CONVERT(varchar(15),StartTime,100),'-',CONVERT(varchar(15),EndTime,100)) AS 'Time',
[Title],[Summary],[SubTitle],T.TrackName,R.Name AS Room,isMnE,
DATENAME(dw, Date) as 'WeekName',
DATEDIFF(MINUTE,StartTime,EndTime) AS Duration
from [dbo].[Sessions] S
INNER JOIN [dbo].[TrackType] T ON T.TrackTypeId=S.[TrackId]
INNER JOIN [dbo].[Room] R ON R.Id=S.RoomId
WHERE isActive=1
SET NOCOUNT OFF;
END
0
Prakash Kumar9y ago
Can you show your procedure?
+1
Sivakumar9y ago
Data come like this , I need Speakers list for sessions

xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SSC.Mobile.Core.Models">
"); background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; vertical-align: bottom; height: 10px;">
2017-01-25T00:00:00
60
PT0S
Ballroom
0
1
i:nil="true"/>
PT0S
i:nil="true"/>
6:25AM-7:25AM
Breakfast
0
i:nil="true"/>
Wednesday
"); background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; vertical-align: bottom; height: 10px;">
2017-01-25T00:00:00
40
PT0S
Ballroom
0
2
i:nil="true"/>
PT0S
i:nil="true"/>
7:25AM-8:05AM
OPENING CEREMONIES
0
i:nil="true"/>
Wednesday
"); background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; vertical-align: bottom; height: 10px;">
2017-01-25T00:00:00
60
PT0S
Ballroom
0
3
i:nil="true"/>
PT0S
"); background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; vertical-align: bottom; height: 10px;">
...
0
Prakash Kumar9y ago
Hi Sivakumar,

First get clear idea what you want, you only posted like
I want to get list of speakers related to particular session.
what actually you want?
+1
Sivakumar9y ago
I want to get all sessions, I'm not passing any parameter to Get Session procedure.
0
Prakash Kumar9y ago
Hi Shivakumar,
Send Session Id as input parameter for the Stored Prodecure, then retrive the data from session table,
create procedure get_sessions(@sessionid as varchar(100))
as
begin

select * from speakers where id in (select speakerid from sessionspeakermapping where sessionid=@sessionid))

end
try this, hope it will works, accept if works.
0