Return an sql command in a Function

Asked

Viewed 2,086 times

0

I wonder if it is possible to return an sql command in a Function to run as a subselect.

For example:

Select 
*, 
produtos = (nome_da_function())
from
Usuario

Because the way I did I could only return as a string inside a column.

The Function I created is the following:

CREATE FUNCTION dbo.RetonarSubselect()
    RETURNS VARCHAR(100)
AS
begin
  declare @subselect varchar(100)
  SET @subselect = (select * from Produto)

  return @subselect 
end
sql sql-server

  • Do you say a user-defined Function of SQL itself? Not that I know, just as string itself.

  • With dynamic SQL it should be possible. If you put the function definition here it might help.

  • You can return TABLE (in which case the query composing subselect is executed before the return).

  • In that case I wouldn’t answer I’ll have to think of another solution to this.

  • Aff! Just now I saw that the question is 2015! But here she appeared as recent...

3 answers

1

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 built the main code, the result of the subconsulta 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, by merging 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.

1

You need to use the function sp_execute_sql within your function, in which you can pass a function such as string, and dynamically assemble your query as a string to be executed

EXECUTE sp_executesql N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee 
 WHERE EmployeeKey = @level', N'@level tinyint', @level = 109;

0

You can create a procedure return a table, call the procedure make a insert in a temporary table and then use this table in queries. From what I saw you want to put the result of a select in a column of another select, this would be something more POO-oriented, where you could add a list, but in SQL it is not possible to do this.

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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.