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
.
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
.
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.
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.– Caique Romero
@user90864 What is the database manager?
– José Diz