Dynamic select query with where clause

Question

I have the following sample data:

-- Table 1: A_Series

create table A_Series
(
    series varchar(10)
);
insert into A_Series values('A101'),('A102'),('A103'),('A104');  

-- Table 1: B_Series

create table B_Series
(
    series varchar(10)
);
insert into B_Series values('B101'),('B102'),('B103'),('B104'); 

Now I have the given input values to search in the series tables based on passed input values.

For example:

DECLARE @input varchar(255) = 'A101,B102,A104'

I want to generate the SELECT statement as shown below:

Expected result:

select series 
from A_Series
where series in ('A101','A104')
union 
select series 
from B_Series
where series in ('B102')

Explaination of above expected result: If the given input value is of A Series then we need to search in A_Series Table, if value is B series then search in B_Series table with the relavent series in WHERE Clause.

My try:

DECLARE @input varchar(255) = 'A101,B102,A104'
DECLARE @query varchar(max) = ''
DECLARE @Series_Where varchar(max) = ''

SET @query = ' SELECT STUFF((SELECT  '' SELECT * FROM [''+cast(name AS varchar(200))+''] UNION ALL '' AS [text()] FROM sys.tables t
                WHERE SUBSTRING(t.name,1,6) IN (SELECT SUBSTRING(Item,3,2)+''_SDR'' FROM udf_Split('''+@input+''','','')) 
                FOR XML PATH('''')
                ), 1, 1, '''')';

PRINT(@query);

Unable to create WHERE as shown in the expected result query.

Answer 1

I find that when doing dynamic SQL a while loop works best for building the SQL. You can easily break it down into little chunks.

EG

    SET NOCOUNT ON;
DECLARE @input varchar(255) = 'A101,B102,A104'
DECLARE @InputT TABLE(Val varchar(255));
DECLARE @SQL VARCHAR(8000)='';
DECLARE @SQL_INNER VARCHAR(255)='';

INSERT @InputT SELECT VALUE FROM string_split(@input, ',');

DECLARE @i INT
DECLARE @search VARCHAR(255);
SET @i = ASCII('A');
WHILE @i <= ASCII('B') -- Set Max Table here'
BEGIN

    SELECT @search = COALESCE(@search + ', ', '') + Val FROM @InputT WHERE Val like CHAR(@i)+'%';

    SELECT @SQL_INNER = 'select series 
from ' + CHAR(@i) + '_Series
    where series in (''' + REPLACE(@search, ',', ''',''') + ''')'

    IF @i >  ASCII('A') SET @SQL += '
UNION ALL
'
    SET @SQL += @SQL_INNER;

    SET @i +=1;
    set @search = NULL;

END
PRINT @SQL

Note - I'm using String_Split() to load my table but any other csv->rows approach can also work. COALESCE() is used to build the Search & REPLACE() adds the extra single quotes.

Answer 2

If you actually just want the result of your dynamically created query there is also no need to use dynamic SQL:

declare @A_Series table (series varchar(10));
declare @B_Series table (series varchar(10));
insert into @A_Series values('A101'),('A102'),('A103'),('A104');  
insert into @B_Series values('B101'),('B102'),('B103'),('B104'); 

declare @input varchar(255) = 'A101,B102,A104';

with s as
(
    select item
    from dbo.fn_StringSplit4k(@input,',',null) as s
)
select a.series
from @A_Series as a
    join s
        on s.item = a.series
union all
select b.series
from @b_Series as b
    join s
        on s.item = b.series;

Output

series
A101
A104
B102

---

String Split function

This is a modified version of Jeff Moden's function:

CREATE function [dbo].[fn_StringSplit4k]
(
     @str nvarchar(4000) = ' '              -- String to split.
    ,@delimiter as nvarchar(1) = ','        -- Delimiting value to split on.
    ,@num as int = null                     -- Which value to return.
)
returns table
as
return
                    -- Start tally table with 10 rows.
    with n(n)   as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1)

                    -- Select the same number of rows as characters in @str as incremental row numbers.
                    -- Cross joins increase exponentially to a max possible 10,000 rows to cover largest @str length.
        ,t(t)   as (select top (select len(isnull(@str,'')) a) row_number() over (order by (select null)) from n n1,n n2,n n3,n n4)

                    -- Return the position of every value that follows the specified delimiter.
        ,s(s)   as (select 1 union all select t+1 from t where substring(isnull(@str,''),t,1) = @delimiter)

                    -- Return the start and length of every value, to use in the SUBSTRING function.
                    -- ISNULL/NULLIF combo handles the last value where there is no delimiter at the end of the string.
        ,l(s,l) as (select s,isnull(nullif(charindex(@delimiter,isnull(@str,''),s),0)-s,4000) from s)

    select rn
          ,item
    from(select row_number() over(order by s) as rn
                ,substring(@str,s,l) as item
        from l
        ) a
    where rn = @num
        or @num is null;