CODEWITHSUNDEEP
sqlsql-server
Chad
Chad

Reputation: 1562

Can I write a query has a conditional table selection

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

Upvotes: 7

Views: 9814

Answers (3)

squillman
squillman

Reputation: 13641

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

Upvotes: 7

JNK
JNK

Reputation: 65187

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.

Upvotes: 6

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

One option is to use Dynamic SQL but if performance isn't an immediate issue, much simpler is to just UNION the tables and add a dummy [table] column to select from.

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

Upvotes: 6

Related Questions