Function within a Function TSQL

Question

Hi,

Can I call a scalar function within a table-valued function?

Thanks

Answer 1

Yes, just as long as the table-valued function returns a table when it's done.

User-defined functions can be nested; that is, one user-defined function can call another. The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. User-defined functions can be nested up to 32 levels. Exceeding the maximum levels of nesting causes the whole calling function chain to fail. Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. Methods invoked from within managed code do not count against this limit.

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

This is very simplistic, but it does work:

--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