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
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.
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.
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With