Procedure with CASE in the WHERE clause

Asked

Viewed 141 times

1

Well my doubt is this, I’m beginner in sql serves, i am running a trial and in it using the CASE method

the following code shows what I am doing:

    ALTER PROCEDURE [dbo].[STP_S_Cliente_Busca](
    @Busca varchar(255),
    @Opao varchar(255)
)
AS
BEGIN

SELECT
    * 
FROM
    tb_cliente 
WHERE
CASE 
    WHEN @Opao = 'NOME' THEN Nome LIKE '%'+ @Busca +'%' 
        WHEN @Opao = 'STATUS' THEN Status_Cliente LIKE '%'+@Busca+'%'
        WHEN @Opao = 'CNPJ' THEN CNPJ LIKE '%'+@Busca+'%'
        WHEN @Opao = 'EMAIL' THEN Email LIKE '%'+@Busca+'%'
        WHEN @Opao = 'TELEFONE' THEN Whatsapp LIKE '%'+@Busca+'%'
END
ORDER BY
    Nome DESC
END

But for some reason does not run, I’ve done this way in mysql and it worked normally, I believe that in sql server have some different detail, but from what I saw in searches done on the internet the case seems right. Am I executing the rule correctly? Or is it really wrong?

I really want to understand what’s wrong.

sql sql-server procedure case

2 answers

1


Jessica, as far as I know it is not possible to put a condition inside Case in SQL Server, I believe it is possible to only return a value. For the case you posted, I suggest the version below, where Case is used to return the value of one of the columns, and the value returned is used in the condition:

WHERE
  CASE 
    WHEN @Opao = 'NOME' THEN Nome 
    WHEN @Opao = 'STATUS' THEN Status_Cliente 
    WHEN @Opao = 'CNPJ' THEN CNPJ
    WHEN @Opao = 'EMAIL' THEN Email
    WHEN @Opao = 'TELEFONE' THEN Whatsapp
  END LIKE '%' + @Busca + '%'

I hope it helps

  • I tested this way, it seems to work when the values are int for example the status, but when I tried to do a search for Name returned me an unexpected error, where it says: Failed to convert the varchar value 'TEST 1' to the type of data int. tried to put a CONVERT VARCHAR plus error persists

  • I like your solution, Silvio.

  • Jessica, I think converting to sweep after Then should solve, but if so I think it would be better to go for something like Jose Diz suggested, or maybe use separate Cases for int and varchar columns.

  • Valeu José Diz.

0

It seems that Mysql implementers have offered something more to developers.

In SQL Server there are some ways to get what you want. For example:

-- código #1
SELECT ...
  from ...
  where (@Opcao = 'NOME' and Nome like '%'+@Busca+'%')
      or (@Opcao = 'STATUS' and Status_Cliente like '%'+@Busca+'%')
      or (@Opcao = 'CNPJ' and CNPJ like '%'+@Busca+'%')
      or (@Opcao = 'EMAIL' and Email like '%'+@Busca+'%')
      or (@Opcao = 'TELEFONE' and Whatsapp like '%'+@Busca+'%')
  order by NOME desc;

Another approach, with the use of CASE, is the following:

-- código #2
SELECT ...
  from ...
  where case when @Opcao = 'NOME'
                  then case when Nome like '%'+@Busca+'%'
                                 then 1
                            else 0 end
             when @Opcao = 'STATUS'
                  then case when Status_Cliente like '%'+@Busca+'%'
                                 then 1
                            else 0 end
             when @Opcao = 'CNPJ'
                  then case when CNPJ like '%'+@Busca+'%'
                                 then 1
                            else 0 end
             when @Opcao = 'EMAIL'
                  then case when Email like '%'+@Busca+'%'
                                 then 1
                            else 0 end
             when @Opcao = 'TELEFONE'
                  then case when Whatsapp like '%'+@Busca+'%'
                                 then 1
                            else 0 end
             else 0 end = 1
  order by NOME desc;

It is written more but is more efficient than code #1 because it only evaluates the expression with LIKE when necessary.

Browser other questions tagged sql sql-server procedure case

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