CODEWITHSUNDEEP
sqlsql-servert-sql
Sowbarani Karthikeyan
Sowbarani Karthikeyan

Reputation: 161

How to use multiple tables using specific condition

I have two table TableA and TableB for these tables data will be populated based on certain process (means TableA will be like temp table if value finialised then data will be moved to TableB)

based on that X condition I will select the value from TableA else from TableB

If X=10 condition 
select col1,col2,col3,col4 from TableA
else
select col1,col2,col3,col4 from TableB

Both tables TableA and TableB will have same table structure,So here i dont want use the same code statement again in else by changing the table name alone.Is any other way that can be used without repeating the select statement again by changing the table.

Upvotes: 0

Views: 192

Answers (4)

Dwight Reynoldson
Dwight Reynoldson

Reputation: 960

The way I would achieve this is with a variable and a union query. The variable allows you to give a true false condition for your where clause so everything is returned from the table you want and nothing from the table you don't want. The structure of the tables allows you to use the query in a union statement so you can see all of the code in one place.

Declare @x int = 10
Select col1, col2, col3, col4 from TableA where @x=10
Union
Select col1, col2, col3, col4 from TableB where @x<>10;

Upvotes: 0

nealkernohan
nealkernohan

Reputation: 836

You can simply roll your condition into a UNION statement.

If @condition = 1 we only select from tableA, else we only select from tableB.

DECLARE @condition bit

SET @condition = 0

SELECT * FROM  [dbo].[TableA] WHERE @condition = 1
UNION
SELECT * FROM  [dbo].[TableB] WHERE @condition != 1

Upvotes: 0

skyflakes
skyflakes

Reputation: 63

or you can also try:

DECLARE @x AS INT
DECLARE @sqlquery AS NVARCHAR(100)

SET @x = 10 --ignore this if you already have a value for x

SET @sqlquery = 'SELECT * FROM '+ CASE WHEN @x = 10 THEN 'TableA' ELSE 'tableB' END

EXEC SP_EXECUTESQL @sqlquery

you can read also: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017

Upvotes: 1

Jervs
Jervs

Reputation: 344

I think you want this:

DECLARE @tableName varchar(20)
IF 1=1
set @tableName = 'table1'
ELSE
set @tableName = 'table2'

EXEC ('SELECT * FROM ' + @tableName)

Upvotes: 1

Related Questions