SQL Server: function as argument for another function

Question

EDIT: Based on answers below I added "dbo." and comments for clarification.

Question: How do I pass a SQL user-defined function (UDF) as an argument for another UDF? Note that the function returns a table.

select dbo.fn_Scalar()                      -- Returns 'sa'.
select * from dbo.fn_Tabler('sa')           -- Returns a table (not scalar).
select dbo.fn_ScalarArg(dbo.fn_Scalar())    -- Returns 'sa'
select * from dbo.fn_Tabler(dbo.fn_Scalar())    -- ERROR: Incorrect syntax near '.'.

(Currently using SQL Server 2008 R2)

EDIT: To ease debugging, here is the sample code to create the above functions:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Scalar]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.fn_Scalar
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Tabler]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.fn_Tabler
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_ScalarArg]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.fn_ScalarArg
GO

create function fn_Scalar()
returns char(200) as
begin
    declare @user char(200)
    select  @user = 'sa'
    return  @user
end
GO

create function fn_Tabler(@User char(200))
returns table as
    return select @User [User]
GO

create function fn_ScalarArg(@User char(200))
returns char(200) as
begin
    declare @u2 char(200)
    select  @u2 = @User
    return  @u2
end
go

@cdhowie: select * from dbo.fn_Tabler(select dbo.fn_Scalar()) yields "Incorrect syntax near the keyword 'select'" and "Incorrect syntax near ')'" — Peet Brits
– Peet Brits, Commented Aug 19, 2011 at 8:06
Badly worded question. You're not trying to pass a function to a function, you're trying to pass the return value of a function to a function. — Denziloe
– Denziloe, Commented Apr 10, 2019 at 16:54

Pranay Rana · Accepted Answer · 2011-08-19 07:49:30Z

1

Its working for me

    select [dbo].[GetOverAllWinLossDetailPointByDay] 
               (1,0,1,dbo.GetOverAllWinLossDetailPointByDay(1,0,1,1))

I think you forgot to add dbo before function name.

answered Aug 19, 2011 at 7:49

Pranay Rana

177k37 gold badges244 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peet Brits Over a year ago

select * from dbo.fn_Tabler(dbo.fn_Scalar()) yields: Incorrect syntax near '.'.

Alex_L · Accepted Answer · 2011-08-19 08:32:48Z

0

BOL says: "Scalar-valued functions must be invoked by using at least the two-part name of the function."

select * from dbo.fn_Tabler(dbo.fn_Scalar())

EDIT: I was wrong, there is the table-valued function... Documentation says that only constants and @local_variables can be passed to table-valued functions (see here)

edited Aug 19, 2011 at 8:32

answered Aug 19, 2011 at 7:56

Alex_L

2,6641 gold badge15 silver badges13 bronze badges

3 Comments

Peet Brits Over a year ago

See my comments to Pranay Rana: fn_Tabler is not a scalar-valued function. It returns a table.

Alex_L Over a year ago

yes, I have tested on my server and retrieved the same error. See the edit for my answer.

Peet Brits Over a year ago

That documentation comment is pretty well hidden in the huge document :-)

Collectives™ on Stack Overflow

SQL Server: function as argument for another function

2 Answers 2

1 Comment

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Related