0

I have a SQL query in which I only want certain conditions checked if an external condition is met—i.e. if a variable matches a specific value, which is set outside the query (part of a Ruby program).

For example, in a database of book info, I have a variable "author" that is populated with a person's name, or "None". I am comparing this book's values with other books, and would want that variable funneled into the SQL query so that if author = 'None', a specified condition is checked. If it is anything other than 'None', the condition can be ignored. Something that would theoretically work like this: 

SELECT *
FROM db AS data
WHERE 1=1
AND data.random_field <> '123' if #{author} <> 'None'

I tried this:

AND NOT (#{author} = 'None' AND data.random_field <> '123')

which obviously doesn't work, because the value of the author variable is interpreted as a field name and not just a variable.

My other thought was to instead get the original author value from a subquery instead of as an outside variable (since the data is originating from the same database). Something like:

SELECT DISTINCT author
FROM db AS data
WHERE 1=1
AND data.id = '[original_book_id]'

then saving that within the query itself as the author variable. I'm not sure which of the two options is doable, or better—inserting the outside variable into the SQL query, or setting the variable within the query and then somehow creating the condition based on that value?

lumos
  • 161
  • 12
  • 1
    Sounds like your describing a "Catch-all query". [Catch-all queries](https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/), [#BackToBasics : An Updated "Kitchen Sink" Example](https://blogs.sentryone.com/aaronbertrand/backtobasics-updated-kitchen-sink-example/) – Thom A Mar 25 '19 at 16:14
  • What kind of syntax is `#{author}`? Is this maybe more of a Ruby question than a SQL question? If so, please tag accordingly. – Tab Alleman Mar 25 '19 at 16:45
  • @TabAlleman While the syntax of that particular part is Ruby, the question doesn't have to do with Ruby. I kept the syntax in to distinguish it from the rest of the SQL code, but my question is specific to dealing with conditions in SQL – lumos Mar 25 '19 at 17:01
  • @lumos if that's true, then here's your duplicate: https://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server – Tab Alleman Mar 25 '19 at 17:51

2 Answers2

1

You could try to accomplish this by constructing a dynamic sql query and adding your condition based on the variable input:

DECLARE @dsql NVARCHAR(MAX),
    @author VARCHAR(100)

SET @dsql = 'SELECT * FROM db AS data WHERE 1=1 '

IF @author = 'None'
BEGIN
    SET @dsql = @dsql + 'AND data.random_field <> ''123'' '
END

EXEC sp_executesql @dsql

If @author = 'None' then the resulting query that is executed would be:

SELECT * FROM db AS data WHERE 1=1 AND data.random_field <> '123'

Otherwise it would be just:

SELECT * FROM db AS data WHERE 1=1
Thermos
  • 181
  • 6
1

You can do like this

SELECT * FROM db AS data 
where 
(@author = 'None' and AND data.random_field <> '123')
or
(@author !='None')
Kemal AL GAZZAH
  • 967
  • 6
  • 15