SQL how to assign table to variable with case when-CodePudding

I have problem where I have 3 tables with date single date column. I need to assign one of these to variable to use as a filter later on in my query. Example will clear this one.

CREATE TABLE #missing_days (trade_date DATE)  
INSERT INTO #missing_days (trade_date) VALUES ('2022-04-10'), ('2022-04-09')

CREATE TABLE #all_days (trade_date DATE)  
INSERT INTO #all_days (trade_date) VALUES ('2022-04-12'), ('2022-04-11'), ('2022-04-10'), ('2022-04-09')

CREATE TABLE #one_day (trade_date DATE)  
INSERT INTO #one_day (trade_date) VALUES ('2022-04-12')

Now I have declared parameter to tell me what I would like to run

DECLARE @what_to_run VARCHAR(10) = 'ALL' --ALL run all dates, MISSING run only missing dates, ONE run only one day

Now depending what value @what_to_run has I need to use correct table in simple WHERE clause later on.

My idea was something like this:

DECLARE @Dates_to_run TABLE
(trade_date DATE)

SET @Dates_to_run = CASE WHEN @what_to_run = 'ALL' THEN (SELECT * FROM #all_days) 
                         WHEN @what_to_run = 'MISSING' THEN (SELECT * FROM #missing_days)  
                         ELSE (SELECT * FROM #one_day) END

but haven't had much success with it.

Expected result would be that when @what_to_run = 'ALL' I would have table with those dates from #all_days to use in simple query such as

SELECT * 
FROM dt
WHERE trade_date IN (SELECT trade_date FROM @Dates_to_run)

I'm using SQL Server.

CodePudding user response：

You must insert into your table variable, maybe this is what you want ?

declare @what_to_run varchar(20) = 'all' 

DECLARE @Dates_to_run TABLE (trade_date DATE)


if @what_to_run = 'ALL'
begin 
     insert into @Dates_to_run
     select * from #all_days 
end
else if @what_to_run = 'MISSING'
begin
     insert into @Dates_to_run
     SELECT * FROM #missing_days
end
else
begin
     insert into @Dates_to_run
     SELECT * FROM #one_day
end

select * from @Dates_to_run

Note that this will only work if the table #all_days, @missing_days and #one_day only have one column and it must be of the datatype DATE

CodePudding user response：

You can use Union and WHERE condition and Subquery by changing variable to get your desired result

DECLARE @what_to_run VARCHAR(10) = 'ALL'
DECLARE @Dates_to_run TABLE
(trade_date DATE)
INSERT INTO @Dates_to_run 
SELECT trade_date FROM 
(SELECT *,'ALL' AS V FROM #all_days
UNION 
SELECT *,'MISSING'  AS V  FROM #missing_days
UNION 
SELECT *,'Oneday'  AS V   FROM #one_day) T
WHERE @what_to_run=T.V