Create function to returned the set of rows bases on the input parameter-CodePudding

I need to create a function where the course category (name) will be used as parameter, and it will return a set of trainings related to it and the number of attendees.

and I don't know where to start. I've done something like these, for the beginning:

CREATE OR ALTER FUNCTION dbo.f_course_cat (@course_cat_name VARCHAR)  
RETURNS table
AS RETURN 
( SELECT
    CASE WHEN @course_cat_name = 'Business' THEN 
    (SELECT course_name, cc.course_category_id
        FROM dbo.course_category CC
        join dbo.course c on c.course_category_id = CC.course_category_id
        WHERE c.course_category_id = 2)
    END
FROM dbo.course_category CC
join dbo.course c on c.course_category_id = CC.course_category_id);
GO

However, I receive an error:

Msg 116, Level 16, State 1, Procedure f_course_cat, Line 10 [Batch Start Line 261]
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I have 3 tables:

SELECT * FROM course_category

course_category_id  course_category_name
2                    Business
1                    Development
3                    Finance & Accounting
4                    IT & Software
6                    Marketing
5                    Personal development

SELECT * FROM course

course_id   course_name                               course_category_id    
1            Agile Kanban: Kanban for Software Development Teams    4   
2            Successful Negotiation: Master Your Negotiating Skill  5   
3            Customer Service: Soft Skills Fundamentals             5   
4            Git & GitHub - The Practical Guide                     1   
5            React - The Complete Guide                             1   
6            Complete C# Masterclass                                1   
7            Learn Parallel Programming with C# and .NET            1   
8            OAuth 2.0 in Spring Boot Applications                  1   
9            Selenium WebDriver with Docker, Jenkins & AWS          1   
10           Microsoft Power BI - A Complete Introduction           2   
11           Facebook Ads B2B: Advertising  Facebook Ads for B2B    6   
12           Business Fundamentals: Marketing Strategy              6   
13           Finance for Non Finance Executives                     3   
14           The Complete Introduction To Accounting and Finance    3   
15           Finance 101: Financial Management & DCF Fundamental    3   
16           Practical Finance                                      3

SELECT * FROM training_history

employee_id course_id   start_date  end_date    training_status_id
1               13      2020-11-17  NULL         2
2               5       2021-06-02  2021-09-03   3
4               7       2020-12-08  2021-06-25   3
3               9       2021-09-08  NULL         1
1               13      2020-11-17  NULL         2
2               5       2021-06-02  2021-09-03   3
4               7       2020-12-08  2021-06-25   3
3               9       2021-09-08  NULL         1

CodePudding user response：

How about just:

CREATE OR ALTER FUNCTION dbo.f_course_cat (@course_cat_name VARCHAR(200)) 
RETURNS table AS 
return
        SELECT course_name, cc.course_category_id
        FROM dbo.course_category CC
        join dbo.course c on c.course_category_id = CC.course_category_id
        WHERE CC.Name = @course_cat_name