how to fetch multiple record by stored procedure

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 

This is procedure where

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

If there is single record its easily fetching

Of there are multiple records, it's throwing an error that subquery returned more than one value

Please help me how retrieve multiple records

Answer 1

Try this:

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

To fetch customer name from all child ids:

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