if else condition in sql

Question

I want to write a sql query that does:

if this query (select X from table1 where x = 'value1') has result return 1 else if (select x from table2 where x = 'value2') has any result return 2 else return 0.

Thanks

Post your question regarding the reading of data sets in C# as a separate question. Or, more appropriately, search for the numerous existing answers and websites that cover that already. — Yuck, Jan 05 '16 at 14:54

Gordon Linoff · Accepted Answer · 2016-01-05T15:05:58.163

7

One method is a select and case:

select (case when exists (select X from table1 where x = 'value1')
             then 1
             when exists (select x from table2 where x = 'value2')
             then 2
             else 0
        end) as flag

edited Jan 05 '16 at 15:05

answered Jan 05 '16 at 14:53

Gordon Linoff

1,242,037
58
646
786

Thanks but how can I read the 'Then' part from c#? how can I access it? – lol Jan 05 '16 at 14:55
2

Suppose you wanted to execute the query `select 1;` from C#. Do you know how to do that? If you do, then you also know how to run Gordon's query from C#. The fact that it uses `case` is irrelevant; they're both just queries that return a single integer. If you don't, then you should look up a tutorial on database access in C#. – Joe Farrell Jan 05 '16 at 15:01

score 1 · Answer 2 · answered Jan 05 '16 at 16:24

Is it possible to implement with variables:

DECLARE @FLAG INT = 0;

SELECT @FLAG = 1 FROM table1 WHERE x = 'value1'

IF @FLAG = 0 
BEGIN
    SELECT @FLAG = 2 FROM table2 WHERE x = 'value2'
END

SELECT @FLAG

The @FLAG variable will hold the value 0, 1 or 2 as the tables contains or not data. If the 1st select does not contain data, then you run the second, if none return data, then return 0 (default @FLAG value).

Skatz1990 · Answer 3 · 2016-01-05T15:10:57.840

0

This should work although it's not an efficient query nor a best practice to use queries like that.

select 
    case when exists (select X from table1 where x = 'value1') then 1 
    when exists (select x from table2 where x = 'value2') then 2 
    else 0 
end;

edited Jan 05 '16 at 15:10

answered Jan 05 '16 at 15:01

Skatz1990

212
7
19

score 0 · Answer 4 · answered Jan 05 '16 at 15:26

Select case is better way to solve this situation when query is short. But when query is long and complex i like to use user defined functions like:

IF dbo.QueryExecution() IS NULL --do something ELSE --do something

And dbo.QueryExecution() can execute your query select X from table1 where x = 'value1'.

On this way it is easier to test and maintain query (at least it is easier to me).

if else condition in sql

4 Answers4