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?
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?
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.
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
.
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*/