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.
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.
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