How to use the output of a select to mount another?

Question

I built a select that concatenates a string. I want to use the result of this select in another select

SELECT * FROM (SELECT CONCAT('caminho da tabela') AS caminho FROM tabela) AS result

Can you do it like this? How can I use?

Answer 1

Does right:

select * 
  from (select * from TABELA1) AS r
 where r.campo = 'eu'

UPDATE MADE AFTER UPDATE OF QUESTION

Your sub query returns a table with a column, to use this in select from the outside just use caminho:

SELECT * FROM (SELECT CONCAT('caminho da tabela') AS caminho FROM tabela) AS result
WHERE result.caminho = 'variavel'

UPDATE AFTER COMMENT

I found how to run sql when it is a string in this reply:

For your problem you may be able to mount an Index of the result in the target table, run as in the linked response and take the value of the table.

Answer 2

Although Ricardo’s response works, this approach can make consultation confusing.

for example, let’s imagine the following hypothetical query:

SELECT 
    c.id
    c.descricao
    d.descricao as alt,
FROM tabelaD as d
LEFT JOIN (
    SELECT 
        ISNULL(a.id, b.id) as id
        ISNULL(a.descricao, b.descricao) as id 
    from (
        SELECT
            a.id,
            a.descricao
        FROM tabelaA as a
    ) a
    full join (
        SELECT
            b.id,
            b.descricao
        FROM tabelaB as b
    ) b ON a.id = b.id
    WHERE a.id is null OR b.id is null
) as c ON d.id = c.id

To help organize this query (and many others), we can use a CTE (common table Expression):

with CTE_A AS (
    select 
        a.id,
        a.descricao
    from tabelaA as a
), CTE_B AS (
    select 
        b.id,
        b.descricao
    from tabelaB as b
), CTE_C AS (
    SELECT 
        ISNULL(a.id, b.id) as id
        ISNULL(a.descricao, b.descricao) as id 
    from CTE_A a
    full join CTE_B b ON a.id = b.id
    WHERE a.id is null OR b.id is null
)

SELECT 
    c.id
    c.descricao
    d.descricao as alt,
FROM tabelaD as d
LEFT JOIN CTE_C as c ON d.id = c.id

for your case, you may have something like this:

WITH CTE_TEXT AS (
    SELECT 
        CONCAT('caminho da tabela') AS caminho 
    FROM tabela
)

SELECT * 
FROM CTE_TEXT AS result

EDIT

Seeing your comment in another answer, I see that what you want is not to reuse a query, but to assemble a query dynamically.

DECLARE @tabela nvarchar(MAX);
DECLARE @coluna nvarchar(MAX);
DECLARE @query nvarchar(MAX);

SELECT 
    @tabela = (SELECT CONCAT('nome da tabela') AS tabela,
    @coluna = (SELECT CONCAT('nome da coluna') AS coluna
FROM consultas;    

SET @query = '
    SELECT *
    FROM {tabela} as a  
    WHERE a.{coluna} = @valor
';

SET @query = REPLACE(@query , '{tabela}', @tabela);
SET @query = REPLACE(@query , '{coluna}', @coluna);

EXEC sp_executesql @query, N'@valor varchar', @valor= 'valor'                             

Note that the variable @query stores a string with its dynamic query.

Names of tabelas and colunas which will be dynamically inserted are between {} and then we replace them with a REPLACE, used {} for convenience only, since C# and Javascript use this notation for string interpolation, you can use the markup you prefer.

While the filters are ideal is to pass them as parameters of the query, for this you will have to put a @ before the name of each parameter, in the above example I am using @valor as parameter. Remember to declare the same in the second argument of sp_executesql, in the example above my @valor is the type varchar and receives the value valor.

Answer 3

I believe this is what you need:

DROP TABLE TabelaComandosSQL;/*Se já existir*/

/*Cria uma tabela para teste -----INÍCIO-----*/
CREATE TABLE TabelaComandosSQL(
[Codigo] [int] NOT NULL CONSTRAINT [PK_TabelaComandosSQL]  DEFAULT ((0)),
[Comando] [nvarchar](MAX)
);
GO
/*Cria uma tabela para teste -----FIM-----*/

/*Cria uma registro (que seria um comando sql) -----INÍCIO-----*/
INSERT TabelaComandosSQL ([Codigo], [Comando]) VALUES (0, 'SELECT * FROM TabelaComandosSQL')
/*Cria uma registro (que seria um comando sql) -----FIM-----*/


DECLARE @sql NVARCHAR(MAX)
/*Faz uma busca na tabela TabelaComandosSQL retornando o comando que deve ser executado*/
SET @SQL = (SELECT TOP 1 [Comando] FROM TabelaComandosSQL)

SELECT (@SQL) [Texto da Consulta];    /*<-Esta linha apenas mostra o comando que foi inserido na tabela TabelaComandosSQL*/
EXEC (@SQL);                        /*<-Esta linha executa o comando que foi inserido na tabela TabelaComandosSQL*/