Category: SQL

How to switch queries in a select statement using filtering

I had a situation where I needed to change a SQL query altogether based on a parameter. So tried nulling and then using
COALESCE, IIF and CASE but they didn’t work all, I came up with a rather inefficient solution. It’s inefficient but I can
scale it for my small queries. The only quirk is that the output is the same, but that is to be expected if you’re try to
transfer data from a controller to a view.

Components:

Query1 and Query2 are two separate queries which output “num” and “person”.

@switchvalue is the parameter value you’re getting from a form or a querystring.

Query:

DECLARE @switchvalue INT = 1
    DECLARE @query1 TABLE (
    Num INT,
    Person VARCHAR(20)
    );
    DECLARE @query2 TABLE (
    Num INT,
    Person VARCHAR(20)
    );
    INSERT INTO @query1 (Num, Person)
    VALUES (0, 'John');
    INSERT INTO @query2 (Num, Person)
    VALUES (1, 'Rebecca');
    SELECT * FROM (
    SELECT 0 AS switch, * FROM @query1
    UNION ALL
    SELECT 1 AS switch, * FROM @query2
    ) a WHERE a.switch = @switchvalue

Filed under: Blog, SQLTagged with: , , ,