how to add a value per parameter to a select statement in sql within a simple table-valued function?

Navigation
1

This is my code and wanted to add a value to a select statement that is passed as parameter for a simple table-valued function. Here is my code: 

CREATE FUNCTION NMELHORESFORNECEDORES(@N INT )
RETURNS TABLE 
AS 
RETURN SELECT TOP @N/*da erro nesta variavel*/  IDFornecedor AS FORNECEDOR, Nome,TotalFactura FROM Fornecedores
JOIN Facturas ON Facturas.Fornecedor=Fornecedores.IDFornecedor
ORDER BY Nome DESC

The error in this @N variable in the SELECT statement after the RETURN, I can concatenate the value in any way with the statement. how can I do it ??

    
asked by anonymous 28.04.2016 / 18:13

1 answer

2

It is necessary to put the expression in parentheses.

See Books Online > TOP (Transact-SQL) - link

For example, this code works: 

USE AdventureWorks2016CTP3;
GO

CREATE FUNCTION dbo.uf_MelhoresFornecedores
(
    @porcento int
)
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (@porcento) PERCENT V.AccountNumber, V.Name, SUM(H.TotalDue) AS 'Total'
        FROM Purchasing.Vendor AS V
            INNER JOIN Purchasing.PurchaseOrderHeader AS H
                ON H.VendorID = V.BusinessEntityID
        GROUP BY V.AccountNumber, V.Name
        ORDER BY Total DESC
);

To test the function, with 10% for example: 

SELECT *
    FROM dbo.uf_MelhoresFornecedores(10);

    
29.04.2016 / 08:03
Jquery test table [closed] ___ ___ erkimt What exactly does the method PDOStatement :: bindColumn ()? ______ qstntxt ___

This method is for database manipulation. I'm not understanding exactly what it does in the column / variable.

    
______ azszpr125912 ___

Used to assign the value of a column of your Select to a variable.

Note that there is a very detailed explanation in the documentation, but to understand you must be familiar with "passages by reference."

See:

%pre%

All the variables passed to the second bindColumn parameter will have the previously specified column value.

In this case, when we make %code% , the variable %code% is created and passes internally to the value of that column, each time the %code% is rotated.

Note that in the third call of %code% in the example, the name of the column where you want to "bind" (reference or pointer) the value for a variable is used.

Note that the second argument of %code% is a reference, so it has the values assigned according to the %code% call.

%pre%

For a better understanding, the functions that have this %code% sign in the argument require a variable, so that additional values can be returned, since %code% is returned something different.

For example, it is the %code% function, which returns a %code% . In it, you can pass two parameters by reference to know what the error or error code is.

%pre%

That is:

%pre%     
___