use if in sql server

Question

I have 3 tables in my database, CUSTOMERS, DISTRIBUTORS and EXPORT and I have a production order table where I take the customer type (Distributor, customer or export) and the ID of each of them. the question is: has as I put a if in the select, to be able to join the 3 tables with the production order? type like this:

if (tipocliente == CLIENTE)
    select CamposDaTabela from OrdemDeProduçao inner join Cliente
if (tipocliente == Distribuidor)
    select select CamposDaTabela from OrdemDeProduçao inner join Distribuidor
if (tipocliente == exportaçao)
    select CamposDaTabela from OrdemDeProduçao inner join Exportaçao

It would be possible to do so within the same sql?

Answer 1

In a nutshell:

You can use the function CASE:

SELECT CAST(
             CASE 
                  WHEN Obsolete = 'N' or InStock = 'Y' 
                     THEN 1 
                  ELSE 0 
             END AS bit) as Saleable, * 
FROM Product

Here has several examples and explanations.

Answer 2

I’m not sure I quite understand the question, but come on.

You could do 3 SELECTS and use UNION to return everything at once, like this:

SELECT CamposDaTabela, CampoCliente 
  FROM OrdemDeProduçao 
 INNER JOIN Cliente ON ...
 WHERE tipocliente = cliente

 UNION 

SELECT CamposDaTabela, CampoDistribuidor 
  FROM OrdemDeProduçao 
 INNER JOIN Distribuidor ON ...
 WHERE tipocliente = distribuidor

UNION 

SELECT CamposDaTabela, CampoExportacao 
  FROM OrdemDeProduçao  
 INNER JOIN Exportaçao ON ...
 WHERE tipocliente = exportacao

UNION

SELECT CamposDaTabela, NULL AS CampoNulo // ou substitua por 0, ou string vazia, dependendo do tipo de dados nas outras selects...
  FROM OrdemDeProduçao  
 WHERE tipocliente = NULL

Answer 3

ELSE (IF... ELSE) (Transact-SQL)

Imposes conditions on the execution of an instruction Transact-SQL. The instruction Transact-SQL (sql_statement) following the Boolean_expression will be executed if the Boolean_expression is assessed as TRUE. The optional keyword ELSE is an instruction Transact-SQL alternative that is executed when the Boolean_expression is assessed as FALSE or NULL.

Arguments

Boolean_expression

It is an expression that returns TRUE or FALSE. If Boolean_expression contains a SELECT statement, it should be included in parentheses.

{ sql_statement | statement_block }

It is any valid Transact-SQL statement or instruction grouping as defined with a block of instructions. To define a block of instructions (batch), use the BEGIN and END keywords of the flow control language. Although all Transact-SQL statements are valid in a BEGIN block... END, certain Transact-SQL statements should not be grouped in the same batch (instruction block).

SET @Number = 50;
IF @Number > 100
   PRINT 'The number is large.';
ELSE 
   BEGIN
      IF @Number < 10
      PRINT 'The number is small.';
   ELSE
      PRINT 'The number is medium.';
   END ;

Answer 4

You can do the way you’re doing, the only difference is that in sql you only use one = to compare.

declare @tipocliente int = 3, @CLIENTE int = 1, @Distribuidor int = 2, @exportaçao int = 3

if (@tipocliente = @CLIENTE)
print 'select CamposDaTabela from OrdemDeProduçao inner join Cliente'
if (@tipocliente = @Distribuidor)
print ' select select CamposDaTabela from OrdemDeProduçao inner join Distribuidor'
if (@tipocliente = @exportaçao)
print 'select CamposDaTabela from OrdemDeProduçao inner join Exportaçao'

Answer 5

So personal...

from the version 2012 SQL Server, it is now possible to use "in line if"

IIF(expression, value_se_true, value_se_false)

Obviously the solutions already presented would give better support/guarantee to the programmer, because they would even work in older versions of SQL Server.