Where based on the value of a variable

Asked

Viewed 22 times

0

I am working on migrating an API to a new platform and this includes migrating queries that were previously written directly into the code to functions in the database. One of the problems I’ve been facing is that some checks we did in the code are not available in SQL Server (or I don’t 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 that case, the condition AND col2 = @var would only be included in the query if the value of @var was 1.

Is there any way to do that?

sql-server

  • The way you’re doing @var will always be 10. Where do you pull the value to set the @var?

  • You can’t put one if so in the where, you can use the conditional function iif() or case for example

  • @Rbz this was just an example that I quickly created to not expose company data. The value of @var, in the case, comes from a POST request.

  • @Ricardopontual using the iif() I can do it within a function?

  • I didn’t quite understand, you say use iif within a Function, or use Function within the iif ?

  • Use the iif within a function. But based on your answer below, I think I’ve managed to solve my problem. I’m just going to do a few more tests to see if it’s really solved.

Show 1 more comment

1 answer

1


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 to col2, or compare col2 to col2, that is, ignore the condition because it will always be varty

  • That one iif(), In addition to facilitating the reading of the code, solved my problem. Thank you!

Browser other questions tagged sql-server

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