SQL Server stored procedure - return flight numbers

Question

I was trying to write a SQL Server stored procedure that would return a list of my flight numbers and based on the price would rate the flight as followed: price <= 500 as Cheap, price >500 and price >=1500 as Normal or else as Expensive

I have tried this but I feel like I'm way off:

create procedure rate_flights
    @result varchar(50) output
as
    declare @fno varchar(100)
    declare @price numeric(10,2)
begin
    set @fno = (select fno from flights)
    set @price = (select price from flights)
    set @result = (select case
                            when @price <= 500 then @fno + ' '+'Φθηνή'
                            when @price > 501 and @price < 1500 then @fno + ' '+ 'Κανονική'
                            when @price > 1500 then @fno + ' ' + 'Ακριβή'
                          end)
end

declare @ret varchar(50)
execute rate_flights @result = @ret out
print @ret

If anyone could point me to the right direction, I would appreciate it.

Thank you in advance.

Answer 1

You are getting the variables (single values) from a select, so you get only the first result for @price and @fno

You should do the calculation at the same time than the select; and probably you don't want to return a varchar but a table.

Start thinking on sets / tables, not standard data types variables; you may use CTEs or table variables if you want, but for SQL databases, you need an alternative programming paradigm

My general suggestion would be something like:

create procedure rate_flights
as
begin
    select fno, price,
    case
      when @price <= 500 then @fno + ' '+'Φθηνή'
      when @price > 501 and @price < 1500 then @fno + ' '+ 'Κανονική'
      when @price > 1500 then @fno + ' ' + 'Ακριβή' 
    end as rate

 from flights

end