MS SQL Server Interview Question:

How To Create an Inline Table-Valued Function?

Submitted by: Administrator

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 GlobalGuideLineDatabase;
GO

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

SELECT counts, id, url FROM Top_Links(999900) ORDER BY counts DESC;
GO
<pre>counts id url
----------- ----------- -----------------------------------
999966 36470 dgqnv qd toqcoupuxortasdtzvc
999953 12292 qebmw ywe q kza wskxqns j
999943 6192 p o qi akk hk od
999923 79161 kv g g
999920 19124 p zoio
999909 90930 xq x y r
(6 row(s) affected)</pre>
Submitted by: Administrator