Execute function that returns multiple values and based on these do the main SELECT - SQL Server

Question

Execute function that returns multiple values and based on these do the main SELECT - SQL Server

Navigation

#1 by (1 votes)

0

I have the following function in SQL Server that returns the name of each column of my table:

CREATE FUNCTION dbo.FiltraNomeColunas(@FiltroColuna VARCHAR(200))
RETURNS VARCHAR
AS
BEGIN
    DECLARE @Retorno VARCHAR(200); 

        SET @Retorno = (
                            SELECT c.name as NomeColuna
                            FROM sys.tables as t
                            INNER JOIN sys.columns c on t.object_id = c.object_id
                            WHERE c.name LIKE @FiltroColuna
                        );  
    RETURN @Retorno
END

I need to give a select in the columns returned by this function.

I'm trying to do as follows:

SELECT 
table_c1_di.E3TimeStamp AS Data_Hora,
dbo.FiltraNomeColunas('%DISJ%')

FROM table_c1_di 
JOIN c1_do.dbo.table_c1_do ON CAST(table_c1_do.E3TimeStamp AS DATETIME2(0)) =  CAST(table_c1_di.[E3TimeStamp] AS DATETIME2(0)) 

WHERE (table_c1_do.E3TimeStamp  >= @DataInicial  AND table_c1_do.E3TimeStamp <= @DataFinal)

ORDER BY table_c1_do.E3TimeStamp  DESC

However, the way I am pulling this one is displaying error because the function returns more than one result.

I would like to know some way that I can be doing / fetching this function so that it returns more values, and since these return values are the name of the columns in this table, that the main select completes the query based on the values of return.

sql-server back-end

asked by anonymous 03.04.2017 / 15:16

1 answer

Node JS and MongoDB - Return data from a model to a route Pass IFDDatasetReference (Firedac) to ClientDataSet.Data

score 1 · Accepted Answer

Here, an example using Table-Value return.

CREATE FUNCTION dbo.FiltraNomeColunas(@FiltroColuna VARCHAR(200))
RETURNS @nomes TABLE 
(
    Nome VARCHAR(200)
)
AS
BEGIN

    INSERT INTO @nomes SELECT c.name as NomeColuna
                            FROM sys.tables as t
                            INNER JOIN sys.columns c on t.object_id = c.object_id
                            WHERE c.name LIKE @FiltroColuna

    RETURN
END

This should resolve the return, which needs to be more than one line.