Function with return using 'case when'

Asked

Viewed 1,012 times

1

I have a Function that returns the status of a request (in the database is as 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 she returns to me 'Inactive Request' or if the result is 1 she returns to me 'Active Request'. Down with 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 'case when', only I constantly found several errors and could not do as I described above.

It turned out that I could not do and I wish someone could help me what I should modify in this Function so that it operates the way I wish.

NOTE: I am using SQL SERVER

sql sql-server

  • what mistake you’re making?

  • I had several mistakes and I wrote down almost nothing, but there was one that I remember that occurred more than once. It was "create Function must be the only statement in the batch". But I believe that I was not able to insert the case when correctly, this I think was the big problem.

  • The relation in your table request returns one to one in the where idPedido = @cod

2 answers

2


right to use the case when would be as follows.

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 of 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 using 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
  • 1

    The second option helped, but has an extra parenthesis after @Cod which should be removed

1

Running on the Sqlserver case that’s how it is:

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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.