How to get multiple list of arrays from stored procedures.

Question

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.

Prakash Kumar · Answer

Can you show your procedure?

Prakash Kumar · Answer

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?

Prakash Kumar · Answer

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.

Prakash Kumar · Answer

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

Sivakumar · Answer

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

Sivakumar · Answer

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;"> ...

Sivakumar · Answer

I want to get all sessions, I'm not passing any parameter to Get Session procedure.

How to get multiple list of arrays from stored procedures.

Answers (7)