I have 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 for one value per parameter to come in, and if that value satisfies if I add and .
There are several solutions, I will mention 3 of them.
In the examples below, I check if the variable @idUsuario is null if it does not search the user name with the idUsuario corresponding to the variable.
Alternative to 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 logical operator use 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)
For conditions, the expression CASE is used.
For example:
SELECT CASE WHEN 1=1 THEN 1 ELSE 0 END
In this query: CASE the condition of 1 is equal to 1 the output will be 1 , otherwise 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