-2

To know doctors who have done 15 or more episodes from tables:

Table 1 table 1

and

table 2

I have written this code:

select 
    d.DoctorName 
from
    tblDoctor d, tblEpisode e 
where 
    e.DoctorId = d.DoctorId
group by 
    d.DoctorId, d.doctorname
having 
    count(d.DoctorID) > 15;

My output:

output

Now I want to create a stored procedure which will output a variable containing a comma-delimited list of the doctors who have done 15 or more episodes.

Can someone help me how to do it?

Heer
  • 15
  • 6
  • 1
    please tag with the proper database platform – OldProgrammer Sep 02 '21 at 12:42
  • Why would you want to modify an already exploitable output to something less convenient such as a serialized data? – Cid Sep 02 '21 at 12:44
  • 3
    [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 replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**almost 30 years** ago) and its use is discouraged – marc_s Sep 02 '21 at 12:45
  • 1
    Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Sep 02 '21 at 13:00
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Sep 06 '21 at 09:56

1 Answers1

0

You could create a stored procedure with an output parameter, and build something using this technique:

declare @test table (Name varchar(10));

insert into @test values ('Tom'),('Dick'),('Harry');

declare @result varchar(20) = '';

select @result = @result + ',' + name from @test

set @result = stuff(@result,1,1,'');

select isnull(@result,'')

But... as mentioned, csv lists are hardly the most useful of formats for returned data, so I would question that before you construct your SP.

Black Light
  • 2,358
  • 5
  • 27
  • 49