"RETURNS TABLE" - Creating Inline Table-Value Functions in SQL Server

Q

How To Create an Inline Table-Valued Function in SQL Server Transact-SQL?

✍: FYIcenter.com

A

To create an inline table-valued function, you need to use the "RETURNS TABLE" clause in the "CREATE FUNCTION" statement. There should be no function body, except for a RETURN statement with a SELECT subquery:

An inline table-valued function can be viewed as a select statement with parameters, see the example showing in this tutorial exercise:

USE FyiCenterData;
GO

CREATE FUNCTION Top_Links(@level INT)
   RETURNS TABLE 
   AS 
   RETURN (SELECT * FROM fyi_links WHERE counts > @level);
GO

SELECT counts, id, url FROM Top_Links(999900) ORDER BY counts DESC;
GO
counts      id          url
----------- ----------- -----------------------------------
999966      36470       dgqnvmy   pyjqd toqcoupuxortasdtzvc
999953      12292          qebmw v qqmywe q  kza  wskxqns j
999943      6192         p o qisvrakk hk od 
999923      79161       kvwwg g
999920      19124       prlg fzoio
999909      90930       xqmeal ikv isx y r 
(6 row(s) affected)

 

Using User Defined Functions in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2016-12-18, 277👍, 0💬