Home >> FAQs/Tutorials >> SQL Server FAQ

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

By: FYIcenter.com

(Continued from previous topic...)

How To Create an Inline Table-Valued Function?

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)

(Continued on next topic...)

1. What Are User Defined Functions?
2. What Are the Differences between User Defined Functions and Stored Procedures?
3. How To Create a Simple User Defined Function?
4. How To Use User Defined Functions in Expressions?
5. How To List All User Defined Functions in the Current Database?
6. How To Drop an Existing User Defined Function?
7. How To Generate CREATE FUNCTION Script on an Existing Function?
8. How To Get the Definition of a User Defined Function Back?
9. How To Modify an Existing User Defined Function?
10. How To Create User Defined Functions with Parameters?
11. How To Provide Values to User Defined Function Parameters?
12. Can You Pass Expressions to Function Parameters?
13. How To Provide Default Values to Function Parameters?
14. How Many Categories of Functions based Their Return Modes?
15. How Many Ways to Create Table-Valued Functions?
16. How To Create an Inline Table-Valued Function?
17. How To Create an Multi-Statement Table-Valued Function?