Geek Answers Handbook

Can I write a query, has a conditional table selection

We have two tables with the same structure and based on a variable. I want to choose which table to choose without having to write 2 queries in my procedure.

Is it 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 it didn’t work

+5
source share
4 answers

To do this, you will need to use dynamic SQL (provided that you want to scale it to more than two tables), which will work, but is suboptimal, since SQL will not generate statistics for it and it will be more difficult to optimize 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
+6
source

If these are just two tables, you can do:

Declare @table = 1

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

UNION ALL

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

@table .

+4

One option is to use Dynamic SQL, but if performance is not an immediate problem, it’s much simpler to just UNIONtable and add columns [table]for selection.

SELECT orderID, Quantity
FROM   (
  SELECT [table] = 1, orderID, Quantity
  FROM   tblOrders
  UNION ALL
  SELECT [table] = 2, orderID, Quantity
  FROM   tblSubscriptionOrders
  ) t
WHERE t.Table = @table
+3
source

You can try this

declare @table int
set @table = 1 

Select orderID, Quantity 
From tblOrders 
Where @table = 1 
And  filled = 0

UNION ALL

Select orderID, Quantity 
From tblSubscriptionOrders 
Where @table = 2
And  filled = 0

or that:

declare @table int
set @table = 1 

if @table = 1
    Select orderID, Quantity 
    From tblOrders 
    Where filled = 0
else if @table = 2
    Select orderID, Quantity 
    From tblSubscriptionOrders 
    Where filled = 0
+2
source

More articles:


All Articles