I wonder if it is possible to return a sql command on a function to run as a subselect.
For example:
Select
*,
produtos = (nome_da_function())
from
Usuario
Well, the way I did it, I was only able to return as a string inside a column.
The function I created is as follows:
CREATE FUNCTION dbo.RetonarSubselect()
RETURNS VARCHAR(100)
AS
begin
declare @subselect varchar(100)
SET @subselect = (select * from Produto)
return @subselect
end
You need to use the sp_execute_sql function inside your function, in it you can pass a function as string , and dynamically mount your query as a string to execute
EXECUTE sp_executesql N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
WHERE EmployeeKey = @level', N'@level tinyint', @level = 109;
In the example you posted, it would be like creating a subtable within each row of the main table. It seems to me that this is not possible in SQL Server. The way you've built the main code, the result of the subquery will have to return a single expression.
If there is no line number limit, create a function of type inline valued-table . That is, the return of the function becomes a table. In the main SELECT command, trigger the function in the FROM clause, through join with the table. If necessary, you can use APPLY, when for each row of the User table the function will be triggered once.
-- código #1
CREATE FUNCTION dbo.RetonarSubselect()
returns table as
return SELECT lista_de_colunas
from Produto
go
With JOIN, we can have:
-- código #2
SELECT U.lista_de_colunas, SS.lista_de_colunas
from Usuário as U
left join dbo.RetonarSubselect() as SS on U.coluna = SS.coluna;
With APPLY, we can have:
-- código #3
SELECT U.lista_de_colunas, SS.lista_de_colunas
from Usuário as U
cross apply dbo.RetonarSubselect(parâmetros) as SS;
where parameter is usually passed to the function, almost always column (s) of some table that is part of the FROM clause.
You can create a procedure that returns a table, call procedure make insert in a temporary table, and then use that table in queries. From what I saw you want by the result of a select in a column of another select, this would be something geared more towards POO, where you could add a list, but in SQL it can not do that.
See if the example below helps.
create proc [dbo].[CallProc]
as
begin
select * from Usuarios
end
DECLARE @tabela table
(
Idusuario int,
Nome varchar(100),
idate int
)
insert into @tabela
exec CallProc
Select
*,
from Usuario U
join @tabela T
on T.Idusuario = U.Idusuario