Как получить несколько записей с помощью хранимой процедуры

Question

select 
   t.Sno, t.childid,
   (select customername as n from customerprofile c where c.cusid = t.childid) as name,t.earnedmoney as commission,
   (select p.bookingamt from propertyregistration p, customerprofile c where p.applicationno = c.applicationno and c.cusid = t.childid) as bookingamt,
   (select p.totalarea from propertyregistration p, customerprofile c where p.applicationno = c.applicationno and c.cusid = t.childid) as totalarea ,
   (select childid from tbl_level where parentid = t.childid) as child
from 
   tbl_level t 
where 
   parentid = @id 

Это процедура, где

(select childid from tbl_level where parentid = t.childid) as child 

Если есть одна запись, ее легко получить

Из нескольких записей выдается ошибка, из-за которой подзапрос возвратил более одного значения.

Пожалуйста, помогите мне, как получить несколько записей

Answer 1

Попробуй это:

SELECT t.Sno, t.childid, c.customername AS NAME, t.earnedmoney AS commission, 
       p.bookingamt AS bookingamt, p.totalarea AS totalarea, 
       MAX(STUFF(A.childid, 1, 1, '')) AS childs
FROM tbl_level t 
LEFT JOIN customerprofile c ON c.cusid = t.childid
LEFT JOIN propertyregistration p ON p.applicationno = c.applicationno 
CROSS APPLY(SELECT ' ' + t1.childid FROM tbl_level t1 WHERE t1.parentid = t.childid FOR XML PATH('')) AS A (childid)
WHERE parentid = @id 
GROUP BY t.Sno, t.childid, c.customername, t.earnedmoney, p.bookingamt, p.totalarea

Чтобы получить имя клиента из всех дочерних идентификаторов:

SELECT t.Sno, t.childid, c.customername AS NAME, t.earnedmoney AS commission, 
       p.bookingamt AS bookingamt, p.totalarea AS totalarea, 
       MAX(STUFF(A.customerNames, 1, 1, '')) AS childs
FROM tbl_level t 
LEFT JOIN customerprofile c ON c.cusid = t.childid
LEFT JOIN propertyregistration p ON p.applicationno = c.applicationno 
CROSS APPLY(SELECT ',' + c1.customername 
            FROM tbl_level t1 
            INNER JOIN customerprofile c1 ON c1.cusid = t1.childid
            WHERE t1.parentid = t.childid 
            FOR XML PATH('')
           ) AS A (customerNames)
WHERE parentid = @id 
GROUP BY t.Sno, t.childid, c.customername, t.earnedmoney, p.bookingamt, p.totalarea