Put If to Where

Asked

Viewed 7,073 times

4

I got the following Where

  WHERE 1 = 1
    AND (0 = 0  OR 1 = 1)
    inicio do if
    AND (2 = 2  OR 3 = 3)
    fim do if

How would I do if?

The idea is that a value per parameter comes and if that value satisfies the if i add the and.

sql
  • 1

    This problem can be solved in many ways, try to provide more information to find the ideal one. As is the condition of your if The variable comes from a previous? Is it Boolean? you can resolver with a dynamic query, with a case or even with comparisons and logical operators.

  • @user90864 What is the database manager?

2 answers

4


There are several solutions, I will mention 3 of them.

In the examples below I check if the variable @idUsuario is void if the user name is not searched with the idUsuario corresponding to variable.

Alternative with CASE WHEN:

DECLARE @idUsuario INT 
SET @idUsuario = 1

SELECT 
    Nome 
FROM
    Usuario
WHERE 
    idUsuario = (
        CASE WHEN @idUsuario IS NULL THEN
            idUsuario 
        ELSE
            @idUsuario 
        END
    )

Alternative with value comparison and use of the logical operator OR to form the filter:

DECLARE @idUsuario INT 
SET @idUsuario = 1

SELECT 
    Nome
FROM
    Usuario
WHERE 
    (@idUsuario IS NULL OR idUsuario = @idUsuario)

Alternative with dynamic query:

DECLARE @query varchar(MAX)

DECLARE @idUsuario INT 
SET @idUsuario = 1

SET @query = 'SELECT Nome FROM Usuario'
IF @idUsuario IS NOT NULL
BEGIN
    SET @query = @query + ' WHERE idUsuario =' + CONVERT(VARCHAR(MAX), @idUsuario)
END 

EXEC (@query)

0

For conditions, the expression CASE.

For example:

SELECT CASE WHEN 1=1 THEN 1 ELSE 0 END

In this query: CASE the condition of 1 be equal 1 the exit will be 1, being contrary 0.

Using case you can use variables as well as columns, p.x:

SELECT CASE @param WHEN 1=1 THEN 1 ELSE 0 END
SELECT CASE table.column WHEN 1 THEN 1 ELSE 0 END

Browser other questions tagged sql

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