Accessing specific row value by id in a scalar SQL function-CodePudding

How can I use a specific value of a field 'ranking' from table 'course' by 'course_id' in a function? I need to return 1 if the value given by a parameter is higher and 0 if the value is lower. So, I need to get the data from the table somehow based on course_id as a parameter.

CREATE OR ALTER FUNCTION dbo.f_rank (@course_id INT, @user_ranking INT)
    RETURNS INT
    AS
    BEGIN
        RETURN 
            CASE 
                WHEN @course_id.ranking > @user_ranking THEN 1
                ELSE 0
            END 
    END

After the function returns 0 or 1 I need to display:

If on call function returns 1 then display ‘Ranking of <course_name> is above score’, else ‘Ranking of <course_name> is below score’.

Sample data:

course_id | course_name | ranking
    1     |     GIT     |   10
    2     |     CSS     |   2
    3     |     C       |   6

I need to compare a ranking of course_id = 1 for example which is 10 with the random number given as a parameter. course_id is also given as a parameter.

For example: If the user chooses as input params (course_id = 1 and user_ranking = 5) Expected result:

'Ranking of GIT is above score' - if function returns 1
'Ranking of GIT is below score' - if function returns 0

CodePudding user response：

I assume that you probably want something like this (no expected results were given on request):

--Note you will have to DROP your old function if already exists as a scalar function first.
--You cannot ALTER a scalar function to be a table function.
CREATE OR ALTER FUNCTION dbo.f_rank (@course_id INT, @user_ranking INT)  
RETURNS table
AS RETURN
    SELECT CONVERT(bit, CASE WHEN C.ranking > @user_ranking THEN 1 ELSE 0 END) AS SomeColumnAlias --Obviusly give this a proper name
    FROM dbo.Course C
    WHERE C.Course_id = @course_id;
GO

As I mentioned, I use an inline table value function, as this will likely be more performant, and you don't mention your version of SQL Server (so don't know if you are on 2019, and so could use an inlineable scalar function).