Inline scalar functions: real or vaporware?
sql, sql-server, t-sql, user-defined-functions
Solution
Well, AFAIK, none exist (not even in the hidden [mssqlsystemresource] database) and there's no syntax to create one. So it appears that this is something that Microsoft must have anticipated in the run-up to SQL Server 2005 by adding a type for it (and doc!), but never actually implemented for some reason.
Though it is one of the single most requested features for all of Ms Sql Server. Primarily because the default UDF's are so slow and we end up having to back-end ITVF's to get the same effect. (difficult and clumsy, but it works).
Problem
What is the correct syntax to create an inline scalar function in SQL Server? Books Online, in the Types of Functions chapter (2005 and up), talks about Inline Scalar Functions as if they exist and as if no `BEGIN...END` block is required (in contrast with multiline functions): For an inline scalar function, there is no function body; the scalar value is the result of a single statement. For a multistatement scalar function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that return the single value. I also noticed a row for "IS: inline scalar function" in the list of object types in the spt_values table: ``` SELECT name FROM master..spt_values WHERE type = 'O9T' AND name LIKE '%function%' ``` I have tried to create such a function with no success: ``` CREATE FUNCTION AddOne(@n int) RETURNS int AS RETURN @n + 1 ``` The error message is Msg 102, Level 15, State 31, Procedure AddOne, Line 3 Incorrect syntax near 'RETURN'. Am I missing something or is there an error in Books Online?