Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I write a query has a conditional table selection

Tags:

sql

sql-server

We have 2 tables with identical structure and based on a variable I want to choose which table to select on with out having to write 2 queries in my procedure.

Is this possible?

I tried

declare @table int
set @table = 1 

Select orderID, Quantity 
from case when @table = 1 then tblOrders else tblSubscriptionOrders end
where filled = 0

But that did not work

like image 673
Chad Avatar asked Sep 23 '11 18:09

Chad


People also ask

Can we write SQL query in if condition?

We can use SQL IF statement without ELSE as well. In the following, the expression evaluates to TRUE; therefore, it prints the message. If the expression evaluates to FALSE, it does not return any output. We should use ELSE statement so that if an evaluation is not TRUE, we can set default output.

How do you write a conditional statement in SQL query?

IF(condition, True, False) from table; An IF statement simple introduces some condition and then returns a result based on whether the condition is true or false.

Can we use two tables in SELECT query?

In SQL we can retrieve data from multiple tables also by using SELECT with multiple tables which actually results in CROSS JOIN of all the tables. The resulting table occurring from CROSS JOIN of two contains all the row combinations of the 2nd table which is a Cartesian product of tables.


2 Answers

You would need to use dynamic SQL for this (assuming you want to scale it to more than just 2 tables), which would work but is suboptimal as SQL will not generate statistics for it and have a harder time optimizing the query.

declare @table sysname
declare @SQL varchar(1000)

set @table = 'MyTable'
SET @SQL='SELECT orderID, Quantity FROM ' + QUOTENAME(@table) + ' WHERE filled=0'

exec sp_executesql @SQL

or, in a stored procedure:

CREATE PROCEDURE p_ConditionalSelect @table sysname
as

declare @SQL varchar(1000)

set @table = 'MyTable'
SET @SQL='SELECT orderID, Quantity FROM ' + QUOTENAME(@table) + ' WHERE filled=0'

exec sp_executesql @SQL
like image 68
squillman Avatar answered Nov 14 '22 21:11

squillman


If it's just two tables you could do:

Declare @table = 1

SELECT *
FROM Table1
WHERE <stuff>
AND @Table = 1

UNION ALL

SELECT *
FROM Table2
WHERE <stuff>
AND @Table = 2

The filter on @table will result in only one of the two halves showing data.

like image 20
JNK Avatar answered Nov 14 '22 22:11

JNK