T-SQL dynamic subquery

Question

T-SQL dynamic subquery

There was such a problem, there is a query in which the table needs to be constantly changed, I would like to put it in a variable, in the form of a separate query, everything is OK, but how can this be pushed into another query? Here's what 'got enough'

--ОК РАБОТАЕТ

declare @table_name varchar(20) = 'rfiles_01022020'
declare @sql varchar(max)
select @sql = 'select r.id from rfiles.dbo.' + @table_name + ' as r '
exec(@sql) 


--ЭТО ТО ЧТО НУЖНО БЕЗ ДИНАМИЧЕСКОГО ЗАПРОСА

select * from MO_BUFFER as m
where exists(select r.id from [rfiles].[dbo].[rfiles_01022020] as r )


--ЭТО УЖЕ НЕ РАБОТАЕТ (

declare @table_name varchar(20) = 'rfiles_01022020'
declare @sql varchar(max)
select @sql = 'select r.id from rfiles.dbo.' + @table_name + ' as r '
select * from MO_BUFFER as m
where exists(exec(@sql))

PS this is for internal operations, if you suddenly wanted to tell me about the dangers of SQL injection

PPS I can't take the entire query into a string, because only a small part of it is shown here, and so it is huge, with a bunch of quotes

---НАЧАЛО ЗАПРОСА
update mo_buffer
set 
    RINTECHERR=case when isnull(RINTECHERR,'')='' then 'РК4' else RINTECHERR+',РК4' end,
    [status] = 2
from MO_BUFFER as m
inner join people as p on p.id = m.people
where m.mo_log = @mo_log
and
(case
    when exists (select r.id from [rfiles].[dbo].[rfiles_01022020] as r where r.id = m.people )
        then

0

sql-server

Author: Петр Иванов, 2020-02-20

Source

3 answers

score 0 · Answer 1

Your subquery is not related to the rest of the selection in any way - run it separately:

DECLARE @SQL    NVARCHAR(MAX)
    , @isExist  bit = 0
    , @TableName    NVARCHAR(500) = N'[rfiles].[dbo].[rfiles_01022020]'

SELECT @SQL = 
    N'IF EXISTS(
        select r.id
        from ' + @TableName + ' as r
    )
        SELECT @isExist = 1;'

EXEC sp_executesql @SQL, N'@isExist bit OUTPUT', @isExist OUTPUT

select * from MO_BUFFER as m
where @isExist = 1

score 0 · Answer 2

You can use a temporary table:

declare @table_name varchar(20) = 'rfiles_01022020'
declare @sql varchar(max)

if object_id('tempdb..#tmp_table') is not null
    drop table #tmp_table;

create table #tmp_table (id int);

select @sql = 'insert into #tmp_table select r.id from rfiles.dbo.' + @table_name + ' as r '
exec(@sql)

select * from MO_BUFFER as m
where exists(select * from #tmp_table)

score 0 · Answer 3

Create a synonym

Create Synonym rfiles for [rfiles].[dbo].[rfiles_01022020]

And use it in your query

select * from MO_BUFFER as m
    where exists(select r.id from [rfiles] as r )

It is clear that after executing the query, you need to delete the synonyms.