Function inside TSQL function

Question

Is it possible to call a scalar function in a table function function?

thank

Answer 1

Yes, as long as the table function returns the table when it is done.

Custom functions can be nested; that is, one user function can call another. The nesting level increases when the called function starts execution and decreases when the called function ends execution. Custom functions can be nested up to 32 levels. exceeding maximum nesting levels a whole chain of calls fail. Any reference to managed code from a user-defined Transact-SQL function is calculated as one level versus a 32-level nested limit. methods are called from within managed do code apart from this limit.

http://msdn.microsoft.com/en-us/library/ms186755.aspx

This is very simplistic, but it works:

--DROP FUNCTION RETURN_INT
--GO
CREATE FUNCTION RETURN_INT ()
    RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
    RETURN 1
END

GO

--DROP FUNCTION RETURN_TABLE
--GO
CREATE FUNCTION RETURN_TABLE ()
    RETURNS @Test TABLE (
    ID INT 
)
WITH EXECUTE AS CALLER
AS 
BEGIN

INSERT INTO @Test
    SELECT DBO.RETURN_INT()
RETURN 
END