Where based on the value of a variable

Question

Where based on the value of a variable

Navigation

#1 by (1 votes)

0

I'm working on migrating an API to a new platform and this includes migrating the queries that were previously written directly into the code for functions in the database. One of the problems I've been facing is that some of the checks we made on the code are not available in SQL Server (or I do not know how to do them).

I need to put a condition of where depending on the value of a variable. For example:

DECLARE @var AS INT = 10;

SELECT * FROM TB_SQL
WHERE col1 = 'StackOverflow'
IF(@VAR = 1) THEN
BEGIN
AND col2 = @var
END
ELSE
BEGIN
-- não coloca condição nenhuma
END

In this case, the condition AND col2 = @var would only be included in the query if the value of @var equals 1.

Is there any way to do this?

sql-server

asked by anonymous 16.08.2018 / 17:26

1 answer

Error running npm install How to perform specific count of custom posts in wordpress

score 1 · Accepted Answer

With the conditional function IIF () you can test a value and compare the field with the variable or itself, which in practice ignores the condition:

DECLARE @var AS INT = 10;

SELECT * FROM TB_SQL
WHERE col1 = 'StackOverflow'
  AND col2 = IIF(@VAR = 1, @VAR, col2)

If @VAR equals 1, compare it with col2, otherwise compares col2 with col2, ie ignores the condition because it will always be vardade