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

What should do this query when a `parentid` has 2 or more profiles (`customerprofile`) or 2 or more rows in `propertyregistration` ? — Bogdan Sahlean, Dec 26 '13 at 09:22
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was discontinued with the ANSI-**92** SQL Standard (more than **20 years** ago) — marc_s, Dec 26 '13 at 09:23
actually i want to show the each child of the particular records — user2922694, Dec 26 '13 at 09:32

Saharsh Shah · Accepted Answer · 2013-12-26T09:54:16.487

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

getting en error Column 'tbl_level.Sno' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. — user2922694, Dec 26 '13 at 09:41
its working sir can you take each record name from customerprofile table of each childid which is fetching out — user2922694, Dec 26 '13 at 09:50
@user2922694 You're most welcome. You can also upvote any question and answer if you find that it is helpful to you by clicking on up arrow above the right mark — Saharsh Shah, Dec 26 '13 at 09:59

how to fetch multiple record by stored procedure

1 Answers1