How can I fix my Function to show different results?

Question

Can someone review my code and guide me what did I do wrong? I managed to create stored function. However, after selecting, the only result for hotellevel is silver.

CREATE FUNCTION HotelLevel
(
    @rating DECIMAL(10,2)
)
RETURNS VARCHAR(20)
AS
BEGIN
    DECLARE @hotel_level VARCHAR(20);

    SELECT @rating = rating
    FROM Hotels;

    IF @rating < 4.10
        SET @hotel_level = 'Blue';    
    ELSE IF (@rating >= 4.10 AND @rating < 4.50)
        SET @hotel_level = 'Bronze'; 
    ELSE IF (@rating >= 4.50 AND @rating < 4.80)
        SET @hotel_level = 'Silver';
    ELSE IF (@rating >= 4.80 AND @rating < 4.90)
        SET @hotel_level = 'Gold';
    ELSE IF @rating >= 4.90
        SET @hotel_level = 'Platinum';

    RETURN @hotel_level;
END

Table Result of storage function

I created this function in MySQL first and it worked. I tried to create the same in SQL Server and that's when something went wrong.

Answer 1

Just to expand on my comment.

Imagine @Tier is an actual table with various tiers for various reasons. This structure can also be used for conditional aggregations and groupings.

Example

Declare @Tier Table ([Tier_Grp] varchar(50),[Tier_R1] money,[Tier_R2] money,[Tier_Desc] varchar(50))  Insert Into @Tier Values 
 ('Hotel Rating',0,4.1,'Blue')
,('Hotel Rating',4.1,4.5,'Bronze')
,('Hotel Rating',4.5,4.8,'Silver')
,('Hotel Rating',4.8,4.9,'Gold')
,('Hotel Rating',4.9,99,'Platnum')
 
Declare @Data Table ([ID] int,[Rating] decimal(10,2))  Insert Into @Data Values 
 (9965,4.7)
,(9966,4.88)
 
Select A.* 
      ,B.Tier_Desc
 From @Data A
 Join @Tier B on Tier_Grp='Hotel Rating' 
             and Rating>=Tier_R1 and Rating<Tier_R2

Results

ID      Rating  Tier_Desc
9965    4.70    Silver
9966    4.88    Gold

Answer 2

The rating is passed as a parameter to you function (@rating). So there is no need to get that value from the table Hotels in your function:

SELECT @rating = rating
    FROM Hotels;

When you leave out this line, because there is no need to change the value of the parameter @rating in your function, your function works as expected.