I'm afraid the answer to this is "NO, you can't", but since I can't find this explained anywhere I'm gonna ask anyway. Given a user defined table type: And a function that returns that type: And another function that accepts that type: Since the return value of fnFind fits the value that is accepted by fnName, I'd hoped that I

How to pass a user defined table type returned from a function as a parameter to another inline function (without ‘DECLARE’)?

I’m afraid the answer to this is “NO, you can’t”, but since I can’t find this explained anywhere I’m gonna ask anyway.

Given a user defined table type:

CREATE TYPE MyType AS TABLE(
    [Id]        UNIQUEIDENTIFIER NOT NULL,
    [Name]      NVARCHAR(200) NOT NULL
)

And a function that returns that type:

CREATE FUNCTION [dbo].[fnFind]
(
    @info UNIQUEIDENTIFIER
)
RETURNS TABLE AS
RETURN( SELECT TOP 1 Id, Name FROM TblUsers WHERE Id = @info );

And another function that accepts that type:

CREATE FUNCTION [dbo].[fnName]
(
    @single MyType READONLY
)
RETURNS TABLE AS
RETURN( SELECT TOP 1 Name, 42 AS NextColumn, 43 AS MoreCols FROM @single );

Since the return value of fnFind fits the value that is accepted by fnName, I’d hoped that I could do something like:

SELECT * FROM SomeOtherTable sot 
INNER JOIN dbo.fnName(dbo.fnFind(sot.Id)) f ON sot.Id = f.Id

-- OR using the select-syntax, since it is a table
SELECT * FROM SomeOtherTable sot 
INNER JOIN dbo.fnName((SELECT * FROM dbo.fnFind(sot.Id))) f ON sot.Id = f.Id

Unfortunately, that syntax leads to errors. Likely, if I add it to a multi-statement script or function I can just assign it and pass it on, but if I want to use a construct like this inside an line TVF, I believe I cannot use DECLARE statements. So I hoped there exists some syntax to do this.

(sorry for the simplicity of the functions above, it’s just to explain the point. Currently the use is within a trigger so I can use multiple statements, but I’d like to use this inside SELECT statements as well, which would require this kind of “functional piping”)

Update: Here’s what I’m trying to achieve. I know I can do it by using cursors or maybe some other magic, but since the logical table inserted has one-or-more rows, I cannot simply declare a variable:

Here fnGetMatchingKey takes a UDTT and fnGetSearchFields returns a table that has the same schema as that UDTT, but this syntax seems to be disallowed:

UPDATE TableX
SET MatchingKeyId = (SELECT TOP 1 KeyId FROM dbo.fnGetMatchingKey(wi.Id, [dbo].[fnGetSearchFields](wi.Id)))
FROM WorkItems wi
INNER JOIN inserted i
ON wi.Id= i.Id

Answer

For a select, I think you are looking for apply:

SELECT *
FROM SomeOtherTable sot CROSS APPLY
     dbo.fnName(dbo.fnFind(sot.Id)) f

I’m not sure if you need the condition sot.Id = f.Id. I’m guessing this is superfluous.

Advertisement

Answer