How to use IF-ELSE like condition in SQL Views?

Question

I want to use IF-ELSE like condition in SQL View as we know we can't use actual IF-ELSE.
Below is an example of how I want to use it:

IF EXISTS ( SELECT    1
          FROM   SomeTable )
SELECT  *
FROM    TableA
ELSE
SELECT  *
FROM    TableB

Also, I don't want to use UNION All. What would be the optimal logic to implement it?

What happens if UNION ALL is the best solution? If the requirement is a select query then union all would be a good solution. If the requirement is a stored procedure then something else could be used. Please tell us what you want this for. — Paul Maxwell, Jan 18 '16 at 05:57
perhaps a table valued function (tvf) wuld suit your need better than a view? e.g. https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx — Paul Maxwell, Jan 18 '16 at 06:45
Look, if you want to use one way and don't want to use another way, then just use your way :) If you can't make that work, then maybe you should suppress your wishes and listen to fellow stackoverflowers to create good solution? — Arvo, Jan 18 '16 at 07:35

Willie Cheng · Answer 1 · 2016-01-18T06:28:17.827

Hopefully It can help you to deal with your question as following code

Simple CASE expression: 
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Arguments

input_expression
Is the expression evaluated when the simple CASE format is used. input_expression is any valid expression.
WHEN when_expression
Is a simple expression to which input_expression is compared when the simple CASE format is used. when_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.
THEN result_expression
Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid expression.
ELSE else_result_expression
Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.
WHEN Boolean_expression
Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

Examples

USE AdventureWorks2012;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

If-else with 2 tables

declare  @a nvarchar(50)
if 1=1 
set @a='select * from table1'
else
set @a='select * from table2'
begin 
 execute sp_executesql   @a
end

Willie, the result-set what I want depends on a TABLE for specific record if it exists or not and accordingly return the data set. Because there are two different tables used in IF-ELSE, I can't use CASE Expression. — varun kumar dutta, Jan 18 '16 at 05:52
@varunkumardutta this is IF-ELSE-CONDITION example ,waiting for a moment and I will try it for you — Willie Cheng, Jan 18 '16 at 06:08
Willie, had it been a Stored Procedure, it was quite easy to implement, but I want to do this in View and in View we can't declare variables. — varun kumar dutta, Jan 18 '16 at 06:33

Paul Maxwell · Answer 2 · 2016-01-18T06:39:40.720

0

For a select query UNION ALL could be an effective solution from what little we do know about your needs. e.g.

DECLARE @judge AS int

SET @judge = (SELECT 1 FROM SomeTable)

SELECT  col1, col2, col3 ...
FROM    TableA
WHERE @judge = 1

UNION ALL
SELECT  col1, col2, col3 ...
FROM    TableB
WHERE @judge <> 1

For a VIEW you might use a CTE for the subquery if it is complex e.g.

;with judge as (SELECT top(1) 1 as x FROM SomeTable ORDER BY 1)

SELECT  col1, col2, col3 ...
FROM    TableA
WHERE 1 = (select x from judge)

UNION ALL
SELECT  col1, col2, col3 ...
FROM    TableB
WHERE 1 <> (select x from judge)

edited Jan 18 '16 at 06:39

answered Jan 18 '16 at 06:03

Paul Maxwell

33,002
3
32
51

We can't DECLARE variables in View. Rather we could use EXISTS in where clause and use UNION ALL. But I want some other way to do this. – varun kumar dutta Jan 18 '16 at 06:14
How do I know you wanted a view? see my comment under the question. explain what you want his solution for **in the question** – Paul Maxwell Jan 18 '16 at 06:35
Like @Used_By_Already to said , please following [Tour](http://stackoverflow.com/tour) and [hot to ask](http://stackoverflow.com/help/how-to-ask) ,you've made confusing to us – Willie Cheng Jan 18 '16 at 06:38

score 0 · Answer 3 · answered Jan 18 '16 at 07:28

You said you'd rather not use UNION ALL but that is the only way you will get this done from a view.

Here's a sample script of what I would do:

CREATE TABLE ta(id INT IDENTITY(1,1) NOT NULL,name NVARCHAR(256));
CREATE TABLE tb(id INT IDENTITY(1,1) NOT NULL,name NVARCHAR(256));
CREATE TABLE tprobe(id INT NULL);
GO

INSERT INTO ta(name)VALUES(N'TT'),(N'Zara'),(N'Peter');
INSERT INTO tb(name)VALUES(N'LL'),(N'Mandy'),(N'Peter');
GO

CREATE VIEW list_names
AS
    SELECT*FROM ta WHERE EXISTS(SELECT 1 FROM tprobe)
    UNION ALL
    SELECT*FROM tb WHERE NOT EXISTS(SELECT 1 FROM tprobe);
GO

SELECT * FROM list_names;
INSERT INTO tprobe(id)VALUES(NULL);
SELECT * FROM list_names;

DROP VIEW list_names;
DROP TABLE tprobe;
DROP TABLE tb;
DROP TABLE ta;
GO

How to use IF-ELSE like condition in SQL Views?

3 Answers3