Function with return using 'case when'

Question

Function with return using 'case when'

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I have a function that returns me the status of a request (in the database it's int) that only has two possible values, 0 (inactive), 1 (active). I was trying to increment it with 'case when' in case the result is 0 it returns 'Inactive Request' or if the result is 1 it returns 'Active Request'. Below is my function:

create function NumPedidoStatus(@cod int)
returns int
as 
begin
    declare @Retorno int
    set @Retorno=(select status from pedido where idPedido = @cod) 
    return @Retorno
end

What I tried to do was take the return value and use it in the 'case when', but I constantly encountered several errors and could not do it the way I described above.

I ended up not being able to do it and I would like someone to help me with what I should modify in this function so that it will work the way I want.

NOTE: I am using SQL SERVER

sql sql-server

asked by anonymous 04.01.2016 / 13:57

2 answers

1

Running SQLServer case looks like this:

SELECT  
   CASE 
      WHEN [colunainteira] = 1 THEN 'um' 
      WHEN [colunainteira] = 0 THEN 'zero'
   END 
  FROM ...

04.01.2016 / 14:17

Error 500 when using .htaccess FireDAC Refresh or RefreshRecord

score 2 · Accepted Answer

sure to use the case when it would look like this.

select case when status = 0 then 'Pedido Ativo' else 'Pedido Inativo' end as Status from pedido where idPedido = @cod)

but in this case you have two options to use Continuing with your command:

 create function NumPedidoStatus(@cod int)
 returns varchar(25)
 as 
 begin
    declare @Retorno int
    set @Retorno=(select status from pedido where idPedido = @cod) 
    if @Retorno = 0
       begin
         return 'Pedido Inativo'
       end
    else 
       begin 
          return 'Pedido Ativo'
       end;
 end

or just using the case when:

 create function NumPedidoStatus(@cod int)
 returns varchar(25)
 as 
 begin
    declare @Retorno varchar(25)
    set @Retorno=(select case when status = 0 then 'Pedido Ativo' 
                         else 'Pedido Inativo' end as Status from pedido 
                   where idPedido = @cod)) 
    return @Retorno
 end