This request

SELECT TOP 1 '[' + Name + '].[dbo].[AMT]' as name FROM master.sys.databases where name like 'CC_%R'

outputs one line [CC_..._58R].[dbo].[AMT] . This line is the name of the current table that I need to work with. I need to substitute this line in the FROM field of my query. The request should not be a function, a trigger, a program, etc. I tried to do this:

 with R as (SELECT TOP 1 '[' + Name + '].[dbo].[AMT]' as name FROM master.sys.databases where name like 'CC_%R') SELECT * FROM R

but this is not correct, since R is a table, not a string.

I am writing the request in Microsoft SQL Server Management Studio (Microsoft SQL Server 2014).

  • This is called "dynamic SQL". And it requires a certain amount of additional body movements. EXECUTE (Transact-SQL) - Akina
  • @Akina, yes, that's exactly what you can do. But I'm looking for an option on how to manage with one request. - Sergey
  • Yes, there are no problems - form a query string that merges the sample received in a dynamic query into a temporary table, execute this query and remove data from this table. - Akina

1 answer 1

Colleagues have already answered you, but I will still give you an example:

 DECLARE @SQL_Script nvarchar(max); SELECT @SQL_Script = 'SELECT 1 as [Column]'; exec sp_executesql @SQL_Script;

I hope you understand the meaning, more: Dynamic SQL statements