Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Function within a Function TSQL

Tags:

function

sql

tsql

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

Thanks

like image 459
D_D Avatar asked Jul 08 '10 16:07

D_D


People also ask

Can you put a function inside a function SQL?

You can nest built in column and scalar functions within other functions. You can nest scalar functions within other scalar functions and within column functions.

What is nested function in SQL?

When one function is called inside the other, it is called a nested function. A nested function is a function defined another function. Result of one function is used as an argument to another function. Example: int E(int x)

Can we use procedure within function?

A procedure cannot be called by a function. DML statments cannot be executed within a function. DML statements can be executed within a procedure. A function can be called within a query.


1 Answers

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
like image 91
kemiller2002 Avatar answered Oct 03 '22 16:10

kemiller2002