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

Question

this is my code and wanted to add a value to a select statement that is passed by parameter to 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 is in the @N variable in the statement SELECT after RETURN, I cannot concatenate the value with the statement at all. how can I do??

Answer 1

It is necessary to put the expression in parentheses.

See Books Online > TOP (Transact-SQL) -- https://msdn.microsoft.com/en-us/library/ms189463.aspx

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);