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

7

I've mounted 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? How can I use it?

    
asked by anonymous 06.01.2016 / 16:31

3 answers

4

Make direct:

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

UPDATE MADE AFTER QUESTION UPDATE

Your sub query returns a table with a column, to use this in the select from outside you only 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 response :

For your problem you might be able to mount an insert of the result in the target table, run as in the linked link, and pick up the value from the table.

    
06.01.2016 / 16:40
3

Although Ricardo's response works, this approach can make a query 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 common table expression (CTE):

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 might 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 mount 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 your dynamic query.

Names of tabelas and colunas that will be inserted dynamically are between {} and then we substitute them with a REPLACE , I used {} just for convenience, because C # and JavaScript use this notation for interpolation of Strings, you can use the markup you prefer.

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

    
06.01.2016 / 17:34
1

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*/
    
06.01.2016 / 17:41