Run function that returns multiple values and based on these make the main SELECT - SQL Server

Asked

Viewed 9,363 times

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 select the columns returned by this function.

I’m trying to do it this way:

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’m pulling this presenting error because the function returns more than one result.

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

database sql-server function back-end

  • In your case it would be better to use a stored procedure in place of a function. One function in its essence, it must return a single value (see examples such as GETDATE() and LOWER()). As you need to return more than one value, use a stored procedure, that can return the result of a select with many values.

  • Another solution would be to declare function returning a table, or Table-Valued return.

1 answer

1


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 solve the return, which needs to be more than one line.

Browser other questions tagged database sql-server function back-end

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