UDFs and input argument expressions

• mcasper

  SSC-Addicted

  Points: 462

  More actions

  September 11, 2003 at 11:29 am

  #106945

  Does anyone know of a documented reason why scalar UDFs accept expressions as input arguments but table-valued UDFs apparently do not?

  Here are sample UDFs that exhibit this behavior on SQL Server 2000 (SP2):

  drop function ufn_inline_table_testargs

  go

  create function ufn_inline_table_testargs

  (@arg int)

  RETURNS table

  AS

  RETURN (

  SELECT 'Data' = 1

  )

  go

  drop function ufn_table_testargs

  go

  create function ufn_table_testargs

  (@arg int)

  returns @tbl table (Data int)

  as

  begin

  insert @tbl (Data) values(1)

  return

  end

  go

  drop function ufn_scalar_testargs

  go

  create function ufn_scalar_testargs

  (@arg int)

  returns int

  as

  begin

  return 1

  end

  go

  select 'Data' = dbo.ufn_scalar_testargs(1+2)

  go

  select * from dbo.ufn_table_testargs(1+2)

  go

  select * from dbo.ufn_inline_table_testargs(1+2)

  Results:

  Data

  -----------

  1

  Server: Msg 170, Level 15, State 1, Line 1

  Line 1: Incorrect syntax near '+'.

  Server: Msg 170, Level 15, State 1, Line 1

  Line 1: Incorrect syntax near '+'.

  Thanks,

  Mark

• September 15, 2003 at 8:00 am

  This was removed by the editor as SPAM

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736162

  More actions

  September 15, 2003 at 10:03 am

  #474211

  Sorry, no idea here, but I haven't done that much with UDFs.

  Steve Jones

  sjones@sqlservercentral.com

  http://www.sqlservercentral.com/columnists/sjones

  The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

  http://www.dkranch.net