8

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

Thanks

1 Answer 1

7

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
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks Kevin, that would help a lot
@Dave, just posted it. If you need anything else, let me know!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.